http://jompesh.com/videos/1/1760/lesson-26-database-normalization
Theory site: http://www.studytonight.com/dbms/generalization-and-specialization.php
Another Resource: http://www.guru99.com/database-normalization.html
http://www.sql-join.com/sql-join-types/
In below: The relationship between the two tables is specified by the customer_id
key, which is the “primary key” in customers table and a “foreign key” in the orders table:
customer_id | first_name | last_name | address | city | state | zipcode | |
---|---|---|---|---|---|---|---|
1 | George | Washington | [email protected] | 3200 Mt Vernon Hwy | Mount Vernon | VA | 22121 |
2 | John | Adams | [email protected] | 1250 Hancock St | Quincy | MA | 02169 |
3 | Thomas | Jefferson | [email protected] | 931 Thomas Jefferson Pkwy | Charlottesville | VA | 22902 |
4 | James | Madison | [email protected] | 11350 Constitution Hwy | Orange | VA | 22960 |
5 | James | Monroe | [email protected] | 2050 James Monroe Parkway | Charlottesville | VA | 22902 |
order_id | order_date | amount | customer_id |
---|---|---|---|
1 | 07/04/1776 | $234.56 | 1 |
2 | 03/14/1760 | $78.50 | 3 |
3 | 05/23/1784 | $124.00 | 2 |
4 | 09/03/1790 | $65.50 | 3 |
Note that (1) not every customer in our customers table has placed an order and (2) there are a few orders for which no customer record exists in our customers table.
1 2 3 4 |
select first_name, last_name, order_date, amount from customer inner join orders on customer.customer_id=orders.customer_id |
Result:
first_name | last_name | order_date | amount | |
---|---|---|---|---|
george | washington | 07/04/1776 | $234.56 | |
john | adams | 05/23/1784 | $124.00 | |
Thomas | Jefferson | 03/14/1760 | $78.50 | |
Thomas | Jefferson | 09/03/1790 | $65.50 |
http://www.sql-join.com/sql-join-types/
In below: The relationship between the two tables is specified by the customer_id
key, which is the “primary key” in customers table and a “foreign key” in the orders table:
customer_id | first_name | last_name | address | city | state | zipcode | |
---|---|---|---|---|---|---|---|
1 | George | Washington | [email protected] | 3200 Mt Vernon Hwy | Mount Vernon | VA | 22121 |
2 | John | Adams | [email protected] | 1250 Hancock St | Quincy | MA | 02169 |
3 | Thomas | Jefferson | [email protected] | 931 Thomas Jefferson Pkwy | Charlottesville | VA | 22902 |
4 | James | Madison | [email protected] | 11350 Constitution Hwy | Orange | VA | 22960 |
5 | James | Monroe | [email protected] | 2050 James Monroe Parkway | Charlottesville | VA | 22902 |
order_id | order_date | amount | customer_id |
---|---|---|---|
1 | 07/04/1776 | $234.56 | 1 |
2 | 03/14/1760 | $78.50 | 3 |
3 | 05/23/1784 | $124.00 | 2 |
4 | 09/03/1790 | $65.50 | 3 |
Note that (1) not every customer in our customers table has placed an order and (2) there are a few orders for which no customer record exists in our customers table.
1 2 3 4 |
select first_name, last_name, order_date, amount from customer inner join orders on customer.customer_id=orders.customer_id |
Result:
first_name | last_name | order_date | amount | |
---|---|---|---|---|
george | washington | 07/04/1776 | $234.56 | |
john | adams | 05/23/1784 | $124.00 | |
Thomas | Jefferson | 03/14/1760 | $78.50 | |
Thomas | Jefferson | 09/03/1790 | $65.50 |
http://www.sql-join.com/sql-join-types/
In below: The relationship between the two tables is specified by the customer_id
key, which is the “primary key” in customers table and a “foreign key” in the orders table:
customer_id | first_name | last_name | address | city | state | zipcode | |
---|---|---|---|---|---|---|---|
1 | George | Washington | [email protected] | 3200 Mt Vernon Hwy | Mount Vernon | VA | 22121 |
2 | John | Adams | [email protected] | 1250 Hancock St | Quincy | MA | 02169 |
3 | Thomas | Jefferson | [email protected] | 931 Thomas Jefferson Pkwy | Charlottesville | VA | 22902 |
4 | James | Madison | [email protected] | 11350 Constitution Hwy | Orange | VA | 22960 |
5 | James | Monroe | [email protected] | 2050 James Monroe Parkway | Charlottesville | VA | 22902 |
order_id | order_date | amount | customer_id |
---|---|---|---|
1 | 07/04/1776 | $234.56 | 1 |
2 | 03/14/1760 | $78.50 | 3 |
3 | 05/23/1784 | $124.00 | 2 |
4 | 09/03/1790 | $65.50 | 3 |
Note that (1) not every customer in our customers table has placed an order and (2) there are a few orders for which no customer record exists in our customers table.
1 2 3 4 |
select first_name, last_name, order_date, amount from customer inner join orders on customer.customer_id=orders.customer_id |
Result:
first_name | last_name | order_date | amount | |
---|---|---|---|---|
george | washington | 07/04/1776 | $234.56 | |
john | adams | 05/23/1784 | $124.00 | |
Thomas | Jefferson | 03/14/1760 | $78.50 | |
Thomas | Jefferson | 09/03/1790 | $65.50 |
http://www.sql-join.com/sql-join-types/
In below: The relationship between the two tables is specified by the customer_id
key, which is the “primary key” in customers table and a “foreign key” in the orders table:
customer_id | first_name | last_name | address | city | state | zipcode | |
---|---|---|---|---|---|---|---|
1 | George | Washington | [email protected] | 3200 Mt Vernon Hwy | Mount Vernon | VA | 22121 |
2 | John | Adams | [email protected] | 1250 Hancock St | Quincy | MA | 02169 |
3 | Thomas | Jefferson | [email protected] | 931 Thomas Jefferson Pkwy | Charlottesville | VA | 22902 |
4 | James | Madison | [email protected] | 11350 Constitution Hwy | Orange | VA | 22960 |
5 | James | Monroe | [email protected] | 2050 James Monroe Parkway | Charlottesville | VA | 22902 |
order_id | order_date | amount | customer_id |
---|---|---|---|
1 | 07/04/1776 | $234.56 | 1 |
2 | 03/14/1760 | $78.50 | 3 |
3 | 05/23/1784 | $124.00 | 2 |
4 | 09/03/1790 | $65.50 | 3 |
Note that (1) not every customer in our customers table has placed an order and (2) there are a few orders for which no customer record exists in our customers table.
1 2 3 4 |
select first_name, last_name, order_date, amount from customer inner join orders on customer.customer_id=orders.customer_id |
Result:
first_name | last_name | order_date | amount | |
---|---|---|---|---|
george | washington | 07/04/1776 | $234.56 | |
john | adams | 05/23/1784 | $124.00 | |
Thomas | Jefferson | 03/14/1760 | $78.50 | |
Thomas | Jefferson | 09/03/1790 | $65.50 |
Good Learning Site for beginners:
Short:
http://www.dofactory.com/sql/left-outer-join
Elaborate:
https://www.w3schools.com/sql/sql_select.asp
Less Important But Okay:
https://www.tutorialspoint.com/sql/index.htm
Questions:
http://programabad.com/questions/5268/sql-normalization
http://www.kdnuggets.com/2015/09/top-20-data-science-moocs.html
https://lagunita.stanford.edu/courses/Home/Databases/Engineering/about
Posted in Data Science, Database
In my 7th semester Level-3 Term -1 of my BSc. Engineering course of Computer Science and Engineering in Daffodil International Univeristy(DIU) I am doing a course named Database Management System
Here our course teacher is using MS SQL Server 2008. I am sharing here what i have learnt from my class.
SQL stands for structured query language which help us to manage database easily
To create database in sql
Suppose a databse for university student
1 |
create database university; |
where we will create a table named student
1 |
create table university.dbo.student(sid int,sname varchar(20),age int); |
after this we can show the empty table by this query
1 |
select * from university.dbo.student; |
Now by this query we will insert data into the table
1 2 3 4 5 6 7 |
insert into university.dbo.student values(01,'Zaki Live',22); insert into university.dbo.student values(02,'Zaki',23); insert into university.dbo.student values(03,'Live',24); insert into university.dbo.student values(04,'Mobin',21); insert into university.dbo.student values(05,'Rahim',20); insert into university.dbo.student values(06,'Rajdip',22); select * from university.dbo.student; |
Don’t forget to use values.It is actually SQL syntax and don’t forget to give
select * from statement at the end of the query as it will help to display the updated table where star or asterisks sign * means all info of this table
if we want to show some limited data from all then here is one example
1 |
select * from university.dbo.student where sid=3; |
1 |
select sname,age from university.dbo.student where sid=3; |
Now I am going to update some of my data in the table then
1 2 |
update university.dbo.student set sname='Rocky Live Dhaka !' where sid=3; select*from university.dbo.student; |
Now I am going to delete some data from the table
1 2 |
delete from university.dbo.student where age=22 or age=23; select * from university.dbo.student; |
Then here see the change.Age name with 22 and 23 have been deleted for this sql command.
We have used here OR statement because AND is not working in MS SQL Server 2008.I don’t know why…May be I don’t know the actual things about and/or in MS SQL. I apologize for this..
If this tutorial helped you to understand the basics of the sql query.Then don’t forget to keep soem comment below.
Thanks for reading 🙂