Data Science Fundamentals with Python and SQL Specialization

 

What you'll learn

Working knowledge of Data Science Tools such as Jupyter Notebooks, R Studio, GitHub, Watson Studio

Python programming basics including data structures, logic, working with files, invoking APIs, and libraries such as Pandas and Numpy

Statistical Analysis techniques including  Descriptive Statistics, Data Visualization, Probability Distribution, Hypothesis Testing and Regression

Relational Database fundamentals including SQL query language, Select statements, sorting & filtering, database functions, accessing multiple tables

Join Free: Data Science Fundamentals with Python and SQL Specialization

Specialization - 5 course series

Data Science is one of the hottest professions of the decade, and the demand for data scientists who can analyze data and communicate results to inform data driven decisions has never been greater. This Specialization from IBM will help anyone interested in pursuing a career in data science by teaching them fundamental skills to get started in this in-demand field.

The specialization consists of 5 self-paced online courses that will provide you with the foundational skills required for Data Science, including open source tools and libraries, Python, Statistical Analysis, SQL, and relational databases. You’ll learn these data science pre-requisites through hands-on practice using real data science tools and real-world data sets.

Upon successfully completing these courses, you will have the practical knowledge and experience to delve deeper in Data Science and work on more advanced Data Science projects. 

No prior knowledge of computer science or programming languages required. 

This program is ACE® recommended—when you complete, you can earn up to 8 college credits.  

Applied Learning Project

All courses in the specialization contain multiple hands-on labs and assignments to help you gain practical experience and skills with a variety of data sets. Build your data science portfolio from the artifacts you produce throughout this program. Course-culminating projects include:

Extracting and graphing financial data with the Pandas data analysis Python library

Generating visualizations and conducting statistical tests to provide insight on housing trends using census data

Using SQL to query census, crime, and demographic data sets to identify causes that impact enrollment, safety, health, and environment ratings in schools

Read More

Python & SQL Mastery: 5 Books in 1: Your Comprehensive Guide from Novice to Expert (2024 Edition) (Data Dynamics: Python & SQL Mastery)

 

Are you poised to elevate your technical expertise and stay ahead in the rapidly evolving world of data and programming?

Look no further!

Our 5 Books Series is meticulously crafted to guide you from the basics to the most advanced concepts in Python and SQL, making it a must-have for database enthusiasts, aspiring data scientists, and seasoned coders alike.

Comprehensive Learning Journey:

Mastering SQL: Dive deep into every facet of SQL, from fundamental data retrieval to complex transactions, views, and indexing.

Synergizing Code and Data: Explore the synergy between Python and SQL Server Development, mastering techniques from executing SQL queries through Python to advanced data manipulation.

Python and SQL for Data Solutions: Uncover the powerful combination of Python and SQL for data analysis, reporting, and integration, including ETL processes and machine learning applications.

Advanced Data Solutions: Delve into integrating Python and SQL for data retrieval, manipulation, and performance optimization.

Integrating Python and SQL: Master database manipulation, focusing on crafting SQL queries in Python and implementing security best practices.

Empower Your Career: Gain the skills that are highly sought after in today's job market. From database management to advanced analytics, this series prepares you for a multitude of roles in tech, data analysis, and beyond.

Practical, Real-World Application: Each book is packed with practical examples, real-world case studies, and hands-on projects. This approach not only reinforces learning but also prepares you to apply your knowledge effectively in professional settings.

Expert Insight and Future Trends: Learn from experts with years of experience in the field. The series not only teaches you current best practices but also explores emerging trends, ensuring you stay at the forefront of technology.

For Beginners and Experts Alike: Whether you're just starting out or looking to deepen your existing knowledge, our series provides a clear, structured path to mastering both Python and SQL.

Embark on this comprehensive journey to mastering Python and SQL. With our series, you'll transform your career, opening doors to new opportunities and achieving data excellence.

Hard Copy: Python & SQL Mastery: 5 Books in 1: Your Comprehensive Guide from Novice to Expert (2024 Edition) (Data Dynamics: Python & SQL Mastery)

Read More

Python Programming and SQL: 5 books in 1 - The #1 Coding Course from Beginner to Advanced. Learn it Well & Fast (2023) (Computer Programming)

 

Supercharge Your Career with Python programming and SQL: The #1 Coding Course from Beginner to Advanced (2024)

Are you looking to turbocharge your career prospects? Do you want to gain the skills that are in high demand in today's job market?

Whether you're a complete beginner or an experienced programmer, this #1 bestseller book is designed to make your learning journey simple, regardless of your current skills. It aims to guide you seamlessly through the content and fast-track your career in no time.

This 5-in-1 guide covers both Python and SQL fundamental and advanced concepts, ensuring that you not only gain a comprehensive understanding but also stand out among your peers and stay ahead of the competition:

✅ Step-by-Step Instructions: This easy-to-understand guide provides step-by-step instructions, making it effortless to grasp Python and SQL fundamentals.

✅ Fast Learning Curve: Progress rapidly from beginner to advanced levels with our carefully crafted curriculum. Gain confidence to tackle coding challenges.

✅ Boost Your Career: Acquire sought-after skills desired by employers, making you stand out in the job market. Get job-ready and attractive to potential employers.

✅ Competitive Edge: Stand out among peers with our cutting-edge course covering fundamentals and advanced concepts. Your coding proficiency will make you invaluable to any organization.

✅ Versatile Job Opportunities: Python and SQL open doors in tech, data analysis, web development, and more. Stay ahead of the competition.

✅ Start Writing Your Own Programs: Empower yourself to create efficient code, unleash creativity, and achieve peak performance.

✅ Real-World Projects: Gain practical experience through hands-on projects, showcasing your coding expertise effectively.

✅ Expert Guidance: Acquire practical skills and knowledge from expert guidance to become a proficient programmer.

Here are just a few things you'll learn in Python programming and SQL:

Get started with Python programming, covering variables, functions, loops, and conditionals

Discover how to work with data in Python, including data types, structures, and manipulation techniques

Learn different data structures, such as sequences, tuples, lists, matrices, and dictionaries

Understand conditional statements and their role in decision making

Discover object-oriented programming (OOP) and learn how to define classes and methods.

Discover the art of exception handling, ensuring robust and error-free code

Explore the power of algorithms, information processing and master the essential features of algorithms

Master file processing in Python, including opening, reading, writing, and appending files, etc.

Master SQL essentials such as basics of SQL, data types, statements, and clauses

Work with databases using SQL, including creating, modifying, and deleting tables and records.

Learn powerful queries: Perform joins, unions, ordering, grouping, and utilize aliases for advanced SQL queries

Explore efficient data management: Navigate MySQL, work with databases, tables, and views

Advanced techniques: explore stored procedures, indexing, truncating, and working with triggers

Master data optimization: Fine-tune SQL queries for optimal performance and efficiency

Gain practical skills and techniques that you can directly apply in you career

Hard Copy: Python Programming and SQL: 5 books in 1 - The #1 Coding Course from Beginner to Advanced. Learn it Well & Fast (2023) (Computer Programming)

Read More

Python Programming and SQL: 5 books in 1 - The #1 Coding Course from Beginner to Advanced. Learn it Well & Fast (2024) (Computer Programming)

 

Supercharge Your Career with Python programming and SQL: The #1 Coding Course from Beginner to Advanced (2024)

Are you looking to turbocharge your career prospects? Do you want to gain the skills that are in high demand in today's job market?

Whether you're a complete beginner or an experienced programmer, this #1 bestseller book is designed to make your learning journey simple, regardless of your current skills. It aims to guide you seamlessly through the content and fast-track your career in no time.

This 5-in-1 guide covers both Python and SQL fundamental and advanced concepts, ensuring that you not only gain a comprehensive understanding but also stand out among your peers and stay ahead of the competition:

✅ Step-by-Step Instructions: This easy-to-understand guide provides step-by-step instructions, making it effortless to grasp Python and SQL fundamentals.

✅ Fast Learning Curve: Progress rapidly from beginner to advanced levels with our carefully crafted curriculum. Gain confidence to tackle coding challenges.

✅ Boost Your Career: Acquire sought-after skills desired by employers, making you stand out in the job market. Get job-ready and attractive to potential employers.

✅ Competitive Edge: Stand out among peers with our cutting-edge course covering fundamentals and advanced concepts. Your coding proficiency will make you invaluable to any organization.

✅ Versatile Job Opportunities: Python and SQL open doors in tech, data analysis, web development, and more. Stay ahead of the competition.

✅ Start Writing Your Own Programs: Empower yourself to create efficient code, unleash creativity, and achieve peak performance.

✅ Real-World Projects: Gain practical experience through hands-on projects, showcasing your coding expertise effectively.

✅ Expert Guidance: Acquire practical skills and knowledge from expert guidance to become a proficient programmer.

Here are just a few things you'll learn in Python programming and SQL:

Get started with Python programming, covering variables, functions, loops, and conditionals

Discover how to work with data in Python, including data types, structures, and manipulation techniques

Learn different data structures, such as sequences, tuples, lists, matrices, and dictionaries

Understand conditional statements and their role in decision making

Discover object-oriented programming (OOP) and learn how to define classes and methods.

Discover the art of exception handling, ensuring robust and error-free code

Explore the power of algorithms, information processing and master the essential features of algorithms

Master file processing in Python, including opening, reading, writing, and appending files, etc.

Master SQL essentials such as basics of SQL, data types, statements, and clauses

Work with databases using SQL, including creating, modifying, and deleting tables and records.

Learn powerful queries: Perform joins, unions, ordering, grouping, and utilize aliases for advanced SQL queries

Explore efficient data management: Navigate MySQL, work with databases, tables, and views

Advanced techniques: explore stored procedures, indexing, truncating, and working with triggers

Master data optimization: Fine-tune SQL queries for optimal performance and efficiency

Gain practical skills and techniques that you can directly apply in you career

And much, much more...

Whether you are a novice or an experienced programmer, this guide aims to make it simple for you to begin your journey and fast-track your career in no time.

Hard Copy: Python Programming and SQL: 5 books in 1 - The #1 Coding Course from Beginner to Advanced. Learn it Well & Fast (2024) (Computer Programming)

Read More

Learn SQL Basics for Data Science Specialization

 

What you'll learn

Use SQL commands to filter, sort, & summarize data; manipulate strings, dates, & numerical data from different sources for analysis

Assess and create datasets to solve your business questions and problems using SQL

Use the collaborative Databricks workspace and create an end-to-end pipeline that reads data, transforms it, and saves the result

Develop a project proposal & select your data, perform statistical analysis & develop metrics, and present your findings & make recommendations

Join Free: Learn SQL Basics for Data Science Specialization

Specialization - 4 course series

This Specialization is intended for a learner with no previous coding experience seeking to develop SQL query fluency. Through four progressively more difficult SQL projects with data science applications, you will cover topics such as SQL basics, data wrangling, SQL analysis, AB testing, distributed computing using Apache Spark, Delta Lake and more. These topics will prepare you to apply SQL creatively to analyze and explore data; demonstrate efficiency in writing queries; create data analysis datasets; conduct feature engineering, use SQL with other data analysis and machine learning toolsets; and use SQL with unstructured data sets. 

Read More

Python, Bash and SQL Essentials for Data Engineering Specialization

 

What you'll learn

Develop data engineering solutions with a minimal and essential subset of the Python language and the Linux environment

Design scripts to connect and query a SQL database using Python

Use a scraping library in Python to read, identify and extract data from websites 

Join Free: Python, Bash and SQL Essentials for Data Engineering Specialization

Specialization - 4 course series

If you are interested in developing the skills needed to be a data engineer, the Python, Bash and SQL Essentials for Data Engineering Specialization is a great place to start. We live in a world that is driven by big data - from what we search online to the route we take to our favorite restaurant, and everything in between. Businesses and organizations use this data to make decisions that impact the ways in which we navigate our lives. How do engineers collect this data? How can this data be organized so that it can be appropriately analyzed? A data engineer is specialized in this initial step of accessing, cleaning and managing big data.

Data engineers today need a solid foundation in a few essential areas: Python, Bash and SQL. In Python, Bash and SQL Essentials for Data Engineering, we provide a nuts and bolts overview of these fundamental skills needed for entering the world of data engineering. Led by three professional data engineers, this Specialization will provide quick and accessible ways to learn data engineering strategies, give you a chance to practice what you’ve learned in integrated lab exercises, and then immediately apply these techniques in your professional or academic life.

Applied Learning Project

Each course includes integrated lab exercises using Visual Studio Code or Jupyter notebooks that give you an opportunity to practice the Python, Bash and SQL skills with real-world applications covered in each course. For each data engineering solution that you explore, you are also encouraged to create a demo video and GitHub repository of code that can be showcased in your digital portfolio for employers. By the end of this Specialization, you will have the foundational skills necessary to begin tackling more complex data engineering solutions.

Read More

Introduction to Structured Query Language (SQL)

 

What you'll learn

Learn about the basic syntax of the SQL language, as well as database design with multiple tables, foreign keys, and the JOIN operation.

Learn to model many-to-many relationships like those needed to represent users, roles, and courses.

Join Free: Introduction to Structured Query Language (SQL)

There are 4 modules in this course

In this course, you'll walk through installation steps for installing a text editor, installing MAMP or XAMPP (or equivalent) and creating a MySql Database. You'll learn about single table queries and the basic syntax of the SQL language, as well as database design with multiple tables, foreign keys, and the JOIN operation. Lastly, you'll learn to model many-to-many relationships like those needed to represent users, roles, and courses.

Read More

PostgreSQL for Everybody Specialization

 

What you'll learn

How to use the PostgreSQL database effectively

Explore database design principles

Dive into database architecture and deployment strategies

Compare and contrast SQL and NoSQL database design approaches and acquire skills applicable to data mining and application development

Join Free: PostgreSQL for Everybody Specialization

Specialization - 4 course series

Across these four courses, you’ll learn how to use the PostgreSQL database and explore topics ranging from database design to database architecture and deployment. You’ll also compare and contrast SQL and NoSQL approaches to database design. The skills in this course will be useful to learners doing data mining or application development.

Applied Learning Project

This course series utilizes a custom autograding environment for an authentic set of graded and practice assignments, including: creating and manipulating tables, designing data models, constructing advanced queries, techniques for working with text in databases, including regular expressions, and more.

Read More

Scripting with Python and SQL for Data Engineering

 

What you'll learn

Extract data from different sources and map it to Python data structures.

Design Scripts to connect and query a SQL database from within Python.

Apply scraping techniques to read and extract data from a website.

Join Free:Scripting with Python and SQL for Data Engineering

There are 4 modules in this course

In this third course of the Python, Bash and SQL Essentials for Data Engineering Specialization, you will explore techniques to work effectively with Python and SQL. We will go through useful data structures in Python scripting and connect to databases like MySQL. Additionally, you will learn how to use a modern text editor to connect and run SQL queries against a real database, performing operations to load and extract data. Finally, you will use extracted data from websites using scraping techniques. These skills will allow you to work effectively when data is not readily available, or when spatial queries are required to extract useful information from databases.

Read More

Data Engineering Foundations Specialization

 

What you'll learn

Working knowledge of Data Engineering Ecosystem and Lifecycle. Viewpoints and tips from Data professionals on starting a career in this domain.

Python programming basics including data structures, logic, working with files, invoking APIs, using libraries such as Pandas and Numpy, doing ETL.

Relational Database fundamentals including Database Design, Creating Schemas, Tables, Constraints, and working with MySQL, PostgreSQL & IBM Db2.

SQL query language, SELECT, INSERT, UPDATE, DELETE statements, database functions, stored procs, working with multiple tables, JOINs, & transactions.

Join Free:Data Engineering Foundations Specialization

Specialization - 5 course series

Data engineering is one of the fastest-growing tech occupations, where the demand for skilled data engineers far outweighs the supply. The goal of data engineering is to make quality data available for fact-finding and data-driven decision making. This Specialization from IBM will help anyone interested in pursuing a career in data engineering by teaching fundamental skills to get started in this field. No prior data engineering experience is required to succeed in this Specialization.

 The Specialization consists of 5 self-paced online courses covering skills required for data engineering, including the data engineering ecosystem and lifecycle, Python, SQL, and Relational Databases.  You will learn these data engineering prerequisites through engaging videos and hands-on practice using real tools and real-world databases. You'll develop your understanding of data engineering, gain skills that can be applied directly to a data career, and build the foundation of your data engineering career.

 Upon successfully completing these courses, you will have the practical knowledge and experience to delve deeper into data engineering and work on more advanced data engineering projects. 

Applied Learning Project

All courses in the Specialization contain multiple hands-on labs and assignments to help you gain practical experience and skills.    

The projects range from working with data in multiple formats to transforming and loading that data into a single source to analyzing socio-economic data with SQL and working with advanced SQL techniques. 

You will work hands-on with multiple real-world databases and tools including MySQL, PostgresSQL, IBM Db2, PhpMyAdmin, pgAdmin, IBM Cloud, Python, Jupyter notebooks, Watson Studio, etc.

Read More

Excel to MySQL: Analytic Techniques for Business Specialization

 

Advance your subject-matter expertise

Learn in-demand skills from university and industry experts

Master a subject or tool with hands-on projects

Develop a deep understanding of key concepts

Earn a career certificate from Duke University

Join Free:Excel to MySQL: Analytic Techniques for Business Specialization

Specialization - 5 course series

Formulate data questions, explore and visualize large datasets, and inform strategic decisions.

In this Specialization, you’ll learn to frame business challenges as data questions. You’ll use powerful tools and methods such as Excel, Tableau, and MySQL to analyze data, create forecasts and models, design visualizations, and communicate your insights. In the final Capstone Project, you’ll apply your skills to explore and justify improvements to a real-world business process.

The Capstone Project focuses on optimizing revenues from residential property, and Airbnb, our Capstone’s official Sponsor, provided input on the project design. Airbnb is the world’s largest marketplace connecting property-owner hosts with travelers to facilitate short-term rental transactions. The top 10 Capstone completers each year will have the opportunity to present their work directly to senior data scientists at Airbnb live for feedback and discussion.

Read More

Database Management System [DBMS]

 Basics of DBMS:

a. Define what a database is and explain its advantages.

b. Differentiate between DBMS and RDBMS.

Relational Database Concepts:

a. Define the terms: table, tuple, attribute, and primary key.

b. Explain the concept of normalization and its importance in a relational database.

SQL Queries:

a. Write an SQL query to retrieve all records from a table named "Employees."

b. Explain the differences between the WHERE and HAVING clauses in SQL.

Database Design:

a. What is the purpose of a foreign key in a relational database?

b. Describe the steps involved in the normalization process.

Transaction Management:

a. Define the ACID properties in the context of database transactions.

b. Explain the concepts of commit and rollback in a database transaction.

Indexing and Query Optimization:

a. Discuss the importance of indexing in a database.

b. Explain how the query optimizer works in a relational database system.

Concurrency Control:

a. What is a deadlock in the context of database concurrency?

b. Discuss the various methods of handling concurrent transactions.

Data Integrity and Constraints:

a. Explain the concept of referential integrity in a relational database.

b. Define the CHECK constraint in SQL.

NoSQL Databases:

a. Compare and contrast SQL and NoSQL databases.

b. Provide examples of NoSQL databases and their use cases.

Database Security:

a. Discuss the importance of database security.

b. Describe techniques for securing a database, including access control and encryption.

Read More

Understanding Basic SQL Syntax

 

What you'll learn

Identify and use correct syntax when writing SQL retrieval queries.

Learn, practice, and apply job-ready skills in less than 2 hours

Receive training from industry experts

Gain hands-on experience solving real-world job tasks

Build confidence using the latest tools and technologies

About this Guided Project

In this project you will learn to identify and use correct syntax when writing SQL retrieval queries. Through hands-on activities in SQLiteStudio, you will gain experience with the SQL syntax used to display specific columns, filter for specific rows, and determine the sequence of those columns and rows in query output. Familiarity with SQL syntax is a marketable skill for both Information Technology professionals and non-IT super users.

Learn step-by-step

In a video that plays in a split-screen with your work area, your instructor will walk you through these steps:

•Describe basic SQL syntax rules along with requirements for the names of tables, and the naming and data type requirements for table columns (fields).

•Use the SELECT command and FROM clause to control the columns that are displayed and the order in which they display in query output.

•Limit the rows retrieved by an SQL query by applying the WHERE clause with one or more conditions.

•Use the ORDER BY clause to modify the sort order of the rows returned from an SQL retrieval query.

•Code an SQL retrieval query that uses the JOIN command to retrieve data rows from tables that are related via a common column.

Join - Understanding Basic SQL Syntax

Read More

Beginning Sensor Networks with Arduino and Raspberry Pi (Technology in Action) by Charles Bell (Author)

Beginning Sensor Networks with Arduino and Raspberry Pi teaches you how to build sensor networks with Arduino, Raspberry Pi, and XBee radio modules, and even shows you how to turn your Raspberry Pi into a MySQL database server to store your sensor data!

First you'll learn about the different types of sensors and sensor networks, including how to build a simple XBee network. Then you'll walk through building an Arduino-based temperature sensor and data collector, followed by building a Raspberry Pi-based sensor node.

Next you'll learn different ways to store sensor data, including writing to an SD card, sending data to the cloud, and setting up a Raspberry Pi MySQL server to host your data. You even learn how to connect to and interact with a MySQL database server directly from an Arduino! Finally you'll learn how to put it all together by connecting your Arduino sensor node to your new Raspberry Pi database server.

If you want to see how well Arduino and Raspberry Pi can get along, especially to create a sensor network, then Beginning Sensor Networks with Arduino and Raspberry Pi is just the book you need.

Buy :

Beginning Sensor Networks with Arduino and Raspberry Pi (Technology in Action) Paperback – 10 Dec 2013 by Charles Bell (Author) 

PDF Download :

Beginning Sensor Networks with Arduino and Raspberry Pi (Technology in Action) Paperback – 10 Dec 2013 by Charles Bell (Author) 

Read More

Microsoft SQL Server 2008, Second Edition

Portable and precise, this pocket-sized guide delivers ready answers for administering SQL Server 2008. Zero in on core support and maintenance tasks using quick-reference tables, instructions, and lists. 

Also get fast facts to : Plan, deploy, and configure SQL Server 2008 Administer server and surface security, access, and network configuration Import, export, transform, and replicate data Manipulate schemas, tables, indexes, and views Automate maintenance and implement policy-based management Monitor server activity and tune performance Manage log shipping and database mirroringPerform backups and recovery

Buy :

Microsoft SQL Server 2008 Administrator's Pocket Consultant Paperback – 2009 by Stanek (Author), William R (Author) 

PDF Download :

Microsoft SQL Server 2008 Administrator's Pocket Consultant Paperback – 2009 by Stanek (Author), William R (Author)

Read More

JOINs

The JOIN is used to combine rows from two or more tables.

SQL Inner Join :-

Select all rows from tables for the match between the columns in tables.
Same as JOIN

Syntax :-
SELECT column FROM table1
INNER JOIN table2
on table1.column = table2.column;

[only matching rows are retrieved]

 Example :-

SELECT emp.eno, emp.ename, dept.dno, dept.dname FROM emp
INNER JOIN dept
on emp.dno = dept.dno; 

SQL LEFT JOIN :-

Returns all rows from the left table, with the matching rows in the right table.
The result is NULL in the right side when there is no match.

Syntax :-

SELECT Columns FROM table1
LEFT [OUTER] JOIN table2
on table1.column = table2.column;
 
Example :-
SELECT emp.eno, emp.ename,dept.dno, dept.dname FROM emp
LEFT JOIN dept
on emp dno = dept.dno; 

SQL RIGHT JOIN :-

Returns all rows from the right table, with the matching rows in the left table.
The result is NULL in the left side when there is no match.

Syntax :-

SELECT columns FROM table1
RIGHT [OUTER] JOIN table2
on table1.column = table2.column;

Example :-

 FULL OUTER JOIN

Returns all rows from the left table and from the right table.
The combines the result of both LEFT and RIGHT joins.

Syntax :-

SELECT columns FROM table1
FULL [OUTER] JOIN table2
on table1.column = table2.column;

Example :-

Read More

UNION Operator

Combines the result of two or more select statement.

Each Select Statement must have same number of columns and columns must have same data types.

Columns should also be in same order.

Syntax :-

[ Removes the duplicate Values ]

SELECT Column1, Column2, FROM table1 
UNION 
SELECT Column1, Column2, FROM table2 

[ Duplicate Values are retained ]

SELECT Column FROM table1
UNION ALL
SELECT Column FROM table2


Example :-

SELECT ename, job FROM emp1
UNION
SELECT ename, job FROM emp2

We have two tables :-

After use UNION  operator we get this table,

a

Read More

FOREIGN KEY

A FOREIGN KEY in one table points to PRIMARY KEY in another table.

A foreign key can have a different name than the primary key it comes from.

The primary key used by a foreign key is also known as a parent key. The table where the primary key is from is known as a parent table.

The foreign key can be used to make sure that the row in one table have corresponding row in another table.

Foreign key value can be null, even though primary key value can't.

Foreign key don't have to be unique in fact, they often aren't.

Create table from use a FOREIGN KEY:-

CREATE TABLE department
(
D_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
D_Name varchar (40),
E_id int,'
CONSTRAINT employee_Eid_fk
FOREIGN KEY (E_id) REFERENCES employee (E_id)

The CONSTRAINT clause allows to define constraints name for the foreign key constraint. If we omit it MySQL will generate a name automatically. It is optional.

The REFERENCES clause specifies the parent table and its columns to which the columns in the child table refer. The number of columns in the child table and parent table specified in the FOREIGN KEY and REFERENCES must be the same.

Read More

ALTER TABLE

This command is used to Add/Change/Modify/Drop existing structure of the table.

ADD Column
Enable/Disable Constraints
Change Column
Modify Column
Drop Column

ADD Column :- When a new column is to be added to the table structure without constraints.

Syntax :-

ALTER TABLE table_name
ADD COLUMN column_name datatype (size);

Example:-

ALTER TABLE my_tab
ADD COLUMN stu_id integer (5);

Change Column :-This is used to change name and data type of an existing column without constraints.

Syntax:-

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name new_data_type (size);

Example:-

ALTER TABLE my_tab
CHANGE COLUMN name student varchar (5);

Modify Column :- This is used to modify size of the data type or the data type itself of an existing column without changing column name.

Syntax:-

ALTER TABLE table_name
MODIFY COLUMN column_name datatype (size);

Example:-

ALTER TABLE  my_tab
MODIFY COLUMN roll integer (10);

DROP COLUMN :- When a column in a table need to delete

Syntax :- 

ALTER TABLE table_name
DROP COLUMN column_name;

Example:-

ALTER TABLE my_tab
DROP COLUMN roll;

When removing constraints from a column

Syntax:-

ALTER TABLE table_name
DROP constraints_name column_name;

Example:-

ALTER TABLE my_tab
DROP UNIQUE KEY (roll);

Read More

SQL The Complete Reference, 3rd Edition Paperback – 1 Jul 2017 by James Groff (Author), Paul Weinberg (Author), Andy Oppel (Author)

SQL The Complete Reference, 3rd Edition

Get comprehensive coverage of every aspect of SQL from three leading industry experts. Revised with coverage of the latest RDBMS software versions, this one-stop guide explains how to build, populate, and administer high-performance databases and develop robust SQL-based applications. 

SQL: The Complete Reference, Third Edition shows you how to work with SQL commands and statements, set up relational databases, load and modify database objects, perform powerful queries, tune performance, and implement reliable security policies. Learn how to employ DDL statements and APIs, integrate XML and Java scripts, use SQL objects, build web servers, handle remote access, and perform distributed transactions. Techniques for managing in-memory, stream, and embedded databases that run on today's mobile, handheld, and wireless devices are included in this in-depth volume.

• Build SQL-based relational databases and applications
• Create, load, and modify database objects using SQL
• Construct and execute simple, multitable, and summary queries
• Implement security measures with authentication, privileges, roles, and views
• Handle database optimization, backup, recovery, and replication
• Work with stored procedures, functions, extensions, triggers, and objects
• Extend functionality using APIs, dynamic SQL, and embedded SQL
• Explore advanced topics such as DBMS transactions, locking mechanisms, materialized views, and two-phase commit protocol
• Understand the latest market trends and the future of SQL

About the Author

James R. Groff is senior vice president of business strategy at Oracle Corporation. He is a SQL expert whose SQL-oriented software company, TimesTen Performance Software, was acquired by Oracle in 2005.

Paul N. Weinberg is senior vice president of NetWeaver MDM at SAP. He is a SQL expert whose SQL-oriented software company, A2i, Inc., was acquired by SAP in 2004. Weinberg is the bestselling author, with James Groff, of the previous editions of this book.

Read More