Sixth Chapter Lesson-5: Different SQL commands of database.

At the end of this lesson-

  • 1. You will be able to write DDL commands  of SQL.
  • 2. You will be able to write DML commands of SQL.
  • 3. You will be able to write DQL commands of SQL.

 

Data Definition Language (DDL): 

Syntax of SQL Command for Creating Database:

CREATE DATABASE database_name;

SQL Command for creating a database named db_sagc

CREATE DATABASE db_sagc;

Syntax of SQL Command for Creating Table: 

CREATE TABLE table_name
(   column_name_1   data_type(size),
    column_name_2   data_type(size), 
    .............................  
    column_name_n   data_type(size)
 );

 SQL command for creating above student_info table-

CREATE TABLE student_info
 (   
	Id 	number PRIMARY KEY,
	Name  	text(20),
	Section	text(5),
	GPA     number,
	City 	text(20),
 );

Syntax of SQL Command for Adding Field On Table: 

ALTER TABLE table_name ADD
( 
	new_column_name_1 	data_type(size), 	
        new_column_name_2 	data_type(size) 
);

SQL Command for adding a field named “Contact”  in above student_info table-

ALTER TABLE student_info ADD (
 	Contact text(25) 
);

After running the SQL command, the structure of student_info table will be like following table-

Syntax of SQL Command for Deleting a Field From Table:

ALTER TABLE table_name DROP column_name;

SQL Command for deleting a field named “City” from the above student_info table-

ALTER TABLE student_info DROP City;

After running the SQL command, the structure of student_info table will be like following table-

Syntax of SQL Command for Deleting a Table From Database:

DROP TABLE table_name ; 

SQL Command for Deleting a Table named “student_info” From the Database-

DROP TABLE student_info ;

 

Data Manipulation Language (DML): 

Syntax of SQL Command for Adding a Record On Table:  

INSERT INTO table_name (column_name_1, column_name_2, ..) VALUES(Value_1, Value_2, ..) ;

SQL Command for adding a record on a “student_info” table – 

INSERT INTO student_info(Id, Name, Section, GPA, City) VALUES( 1, “Ashek", ”A”, 5.00, "Dhaka“ ) ;

After running the SQL command, the student_info table will be like following table-

Syntax of SQL Command for Updating Record of a Table: 

UPDATE table_name 
SET column_name_1=value, column_name_2=value, ... 
WHERE condition;

SQL command for Updating the value of  City field whose Id is 2 – 

UPDATE student_info SET City="Khulna“ WHERE Id = 2;

After running the SQL command, the student_info table will be like following table-

Syntax of SQL Command for Deleting Record from Table:

DELETE FROM table_name WHERE condition;

SQL Command for deleting the record whose Id is 1 from student_info table-

DELETE FROM student_info WHERE Id = 1 ;

After running the SQL command, the student_info table will be like following table-

Syntax of SQL command for deleting all the records from a table.

DELETE FROM table_name;

 

Data query language (DQL):

Syntax of SQL Command for showing all the fields and records of a table-

SELECT * FROM table_name ;

SQL Command for showing all the fields and records from above “student_info” table- 

SELECT * FROM student_info;

After running the SQL command, the output table will be like following table-

Syntax of SQL Command for showing particular fields and all the records of a table-

SELECT column_name_1, column_name_2, .. FROM table_name ;

SQL Command for showing the value of Name,Section and GPA fields from the above “student_info” table-

SELECT Name, Section, GPA FROM student_info;

After running the SQL command, the output table will be like following table-

Syntax of SQL Command for showing particular records of a table under condition –

SELECT * FROM table_name WHERE condition ; 

SQL Command for showing the records whose GPA is 5.00 from the above “student_info” table-

SELECT * FROM student_info WHERE GPA=5.00; 

After running the SQL command, the output table will be like following table-

 

Lesson Evaluation-

Knowledge Based Questions:

Comprehension Based Questions:

Creative Questions:

Multiple Choice Questions:

 


Written by,

Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *