CREATE , ALTER , INSERT , DROP , DELETE , PRIMARY KEY , FOREIGN KEY: Enhancing(SQL) (Part-2)

PRE-REQUISTE

Read Part 1st if you are not aware of all keys and anomalies in database

PART-1 (Understand concept of all keys (theory) and what are anomalies in database)

Creating first table ✅

Here I am created a table name as "customer" which contains column like "cust_id" , "age" , "location" , "gender" and what I going to do that I created "cust_id" as a Primary Key.

Primary Key

The key which is unique . Let me elaborate a little primary key a column which contains unique data . For example roll no. of students - 33,54 76 ,77,21,66 as you can see that the roll no. are unique and are not repeated . So this kind of data contained by primary key.

INPUT

create table customer (
cust_id int primary key,
age int,
location varchar(20),
gender varchar(20)
);

I have created customer table as you can see 👇👇

OUTPUT

Understanding the Command of "customer" table

create table customer ( - "create" command comes under DDL as I have defined a new table so the syntax is create table table name bracket open.

cust_id int primary key, - "cust_id" is my first column and its a primary key as you all can see very well but before that its data type is "int" which means this column can only contain an integer value like 33,66545,4323432423,3,23 .

age int, - Here I took another column by separating with ',' and it is mandatory to mention datatype after specifying column name as you can see I mentioned "int".

location varchar(20), - Here I took another column by separating with ',' and column name is "location" as you can see I mentioned "varchar(20)". "varchar(20)" means it can contain "aplhanumeric" data and total length it can hold is 20. For example - 3345,NH-street,near brampton.

gender varchar(20) - Here I took another column by separating with ',' and column name is "gender" as you can see I mentioned "varchar(20)" again.

); - At the last ended this command with a bracket close and a semicolon.

Creating second table ✅

Here I am created a table name as "orders" which contains column like "order_id" , "Datei" , "amount" , "custid" and what I going to do that I created "order_id" as a Primary Key and "custid" as a Foreign Key.

Foreign Key

In simple words . It's a column which is "Primary Key" in another table.

INPUT

create table orders (
order_id int,
Datei date,
amount decimal(5,2),
custid int,
primary key (order_id),
FOREIGN KEY (custid) REFERENCES customer(cust_id) ON DELETE CASCADE
);

I have created orders table as you can see 👇👇

OUTPUT

Understanding the Command of "orders" table

create table orders ( - created table with name "orders"

order_id int, - first column created with datatype int which name is "order_id" and as you know that it contain only numeric values for example 34,782,5,787978 etc.

Datei date, - Second column is created with name as "Datei" and it can contain only date as a data.

amount decimal(5,2), - Now third column will contain a value which contains decimal point before 2 digits and the total length of that number should be 5 digits. For example 234.56 ,273.64 ,124.54 etc.

custid int, - Fourth and the last column contain type of data "int" and it is the same "cust_id" which is the primary key of the customer table. Now here I have named it as "custid" and it is Foreign key in this orders table. But the type must be the same we cannot change the type of data of a column which we are using as a Foreign key we can only change the name if you are not clear that what I am trying to explain just be chill and keep up reading the blog you will find your answer.

primary key (order_id), - Here I have created order_id as a primary key.

FOREIGN KEY (custid) REFERENCES customer(cust_id) ON DELETE CASCADE - In this line I have created foreign key custid and given the reference that this custid is the primary key of customer table and in that table its name is cust_id so make this column as a foreign key . "ON DELETE CASCADE" means that when you delete the data from customer table it should automatically be deleted from orders table.

);

Creating a Copy of a table. ✅🖨

As I have created two tables one is customer table and another one is orders table. Now I am going to create a copy of same orders table and I will name it as orders_copy , it contains all the same column and stuff.

INPUT

create table orders_copy like orders;

I have created orders_copy table as you can see 👇👇

OUTPUT

All the columns are same because table is copied with a different name.

Adding a new column to the existing table ➕

Here , I have added a new column in the orders_copy table which is "email" using alter command which also comes under DDL(Data Definition Language). I have added a new column. Command is - "alter table than specify table_name add column_name which you want to add and type of data which that column requires" simple 👍 . There requirement of specifying the type of data that column needs at the end of the command 👇👇.

INPUT

alter table orders_copy add email varchar(20);

OUTPUT

Here is the output with the command I have used 👇👇

Dropping a column from existing table 🗑

Now, here I have dropped the a column. Command is same instead of using add I have used drop and only specify the column name you can see how I did. There is no requirement of specifying the type of data that column needs.👇👇

INPUT

alter table orders_copy drop email;

OUTPUT

As you all can see the "email" column is dropped from the table. 👇👇

Inserting Data to the table ✅

Now the time comes when I have inserted the data in the table. Command is simple again - "insert into table_name values (first value as I know my first column of customer table requires int value as its a cust_id , second column also requires int value because its an age column , third column requires a varchar which should be comes in ' ' single quotes as its a location we can insert alpha-numeric value in it using ' ' single quotes , fourth column also requires a varchar which should be comes in again ' ' in single quotes its a gender ); " this data will fill in the first row of the table.

INPUT

insert into customer values(1000,42,'Vegas','Female');

OUTPUT

As you all can see data is updated in the table 👆👆

Inserting multiple data at one time ✅

Let me show you how to insert data to multiple row on one go. Command is simple and same again just separate data brackets with a comma and you can insert 'n' number of rows in the table. See👇👇.

INPUT

insert into customer values
(1001,66,'Dallas','Female'),
(1002,25,'Colorado','Male');

OUTPUT

Data is inserted successfully . Data in the first row was already there and after that in next two rows data is inserted 👆👆.

Lets insert some more data to the table.

INPUT

insert into customer values
(1003,16,'Dallas','Female'),
(1004,26,'Colorado','Male'),
(1005,66,'Sanfrancisco','Male'),
(1006,25,'Texas','Female');

OUTPUT

Data is inserted successfully . Now, from 1000 to 1006 I have cust_id as its my primary key it will contain only unique data in other columns data is repeated but not in the first column.

**Let me show with example of unique data.**👇👇

ERROR ❌

INPUT

insert into customer values
(1001,11,'Bay area','Male');

OUPUT

Error occurred 😡 why! because I made a mistake can you guys guess what mistake I made. 🧐 Let me show you the error is saying in its first line that "Duplicate entry '1001' for key customer.PRIMARY" it means that as my primary key is cust_id in the customer table I have entered the duplicate value as I already told you that the primary key can only hold the unique data which should not be repeated again and again. Also you must have observed that their is no changes in the table. Data in 1001 is still same and its not inserted or updated.

Foreign key constraint "fails" ❌

As you all know that we have three tables - customer , orders & orders_copy table and I assume you , may know that the orders_copy is same table just a copy of orders table . So, the point is that I going to insert data in the orders table and then in the last column which is foreign key "custid" I will enter "1007" id which is not presented in the customer table.

SELECT command

If you want to check what's their in my table then you can run select command let me show you.👇👇

INPUT

select * from customer;

Syntax is very clear just write- "select '*' - asterisk sign means all from - means from which table Table_Name "

You can see that their is no '1007'. As you must know that "cust_id" is foreign key in another table the named as "custid" it means they are co-related. Lets see what happens.

insert into orders values
(101,'2007-05-16',299,1007);

OUTPUT

The Error states that "cannot add or update a child row: a foreign key contraint fails" it simply means that '1007' cust_id is not presented in the customer table how can you insert data in the orders table with '1007' custid as it is not presented in customer table.

But what if I insert an existing custid than lets see what will happen**.**👇👇

INPUT

insert into orders values
(100,'2020-12-12',499,1000),
(101,'2018-11-14',660,1001),
(102,'2012-08-23',199,1002),
(103,'2003-06-21',499,1003),
(104,'2010-02-12',499,1004),
(105,'2006-05-04',660,1005),
(106,'2002-09-11',299,1006);

OUTPUT

Data is successfully inserted into the table

How to delete a row in a table.

Here we will see that how to delete data from a table.

This is my data currently in the orders table.👇👇

Now I am going to delete the data which is on the third row.

INPUT

delete from orders where order_id = 102;

OUTPUT

As you all can see that 102 order_id is deleted and entire row as well.

Let's understand the command

delete from orders where order_id = 102;

delete is a command in SQL , from is a keyword then table_name , where is again a keyword then column_name , acts like a condition.

Let me show you how I can delete a row with different condition. Till now I have deleted row using primary key as order_id is a primary key in orders table.

INPUT

delete from orders where custid = 1006;

OUTPUT

Here as you can see last row is deleted. Till now I have deleted row using primary key as custid is also a primary key in customer table.

Let's see what will happen when I delete any row using amount which is not a primary key .

INPUT

delete from orders where amount = 499;

OUTPUT

Now all those rows are deleted which were containing amount 499 it means you can delete data according to your choice or condition.

Now I am adding some data to the table .

INPUT

insert into orders values
(100,'2020-12-12',799,1000),
(101,'2018-11-14',660,1001),
(102,'2012-08-23',199,1002),
(103,'2020-12-12',999,1003),
(104,'2010-02-12',499,1004),
(105,'2020-12-12',660,1005),
(106,'2002-09-11',299,1006);

OUTPUT

As you can see that I have inserted data in many rows but I want to see data which have date 2020-12-12 and amount less than 800 then I will use this command 👇👇

INPUT

select * from orders where Datei = '2020-12-12' and amount <800

OUTPUT

Now as you all can see that this is the data which met my condition.

END