GRADE 12 DDL AND DML STATEMENTS WITH PROJECT

 

TYPES SQL COMMANDS

DDL AND DML STATEMENTS WITH EXAMPLES

DDL STATEMENTS: 

CREATE COMMAND:

Create database or its objects (table, index, function, views, store procedure, and triggers)

SYNTAX: CREATE DATABASE DATABASE_name;

Syntax: CREATE TABLE table_name (column1 data_type, column2 data_type, ...);

Create a database name BOOKSTORE

è CREATE DATABASE BOOKSTORE;

Create a table name book_details. Under the book details create table field Book id, Name, Author name and publisher name)

è CREATE TABLE BOOK_DETAILS (Bid INT, Book_Name varchar(30), Author_name varchar(30), Publisher_name varchar(30));

Create a NEW table name STUDENTS. Under the Student create table field student id, Name, Address and phone)

è CREATE TABLE students (sid INT, Name varchar(30), Address varchar(30), Phone double);

DROP COMMAND

Delete objects,/table  from the database. It also used to delete database.

SYNTAX: DROP TABLE table_name;

Delete the table name students from the database book store.

è          DROP TABLE STUDENTS;

TRUNCATE COMMAND:

Remove all records from a table, including all spaces allocated for the records are removed

SYNTAX: TRUNCATE TABLE table_name

To delete all the records from table:

è TRUNCATE TABLE BOOK_DETAILS;

RENAME COMMAND:

Rename an object existing in the database

SYNTAX: RENAME TABLE old_table_name TO new_table_name;

Write a sql command to rename the table book_details into book_info

RENAME TABLE BOOK_DETAILS TO BOOK_INFO

 

DML STATEMENTS

INSERT COMMAND:

Insert data into a table

SYNTAX: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Inserting 5 records in a table book_INFO

è INSERT INTO BOOK_INFO (Bid, Book_Name, Author_name, Publisher_name) VALUES (101, “Computer Science”, “Dinesh Aryal”, “Ashmita Publication”);

BID	BOOK_NAME	Author_name	Publisher_name
101	Computer Science	Dinesh Aryal	Ashmita Publication
102	Accountancy II	Arjun Lamsal	Buddha
103	Economics	Arpita Gurung	Horizon
104	Nepali	CDC	Nepal Government

 DELETE COMMAND:

Delete records from a database table

SYNTAX: DELETE FROM table_name WHERE condition;

TO DELETE THE RECORDS

è DELETE FROM BOOK_DETAILS WHERE Bid= “101”;

BID	BOOK_NAME	Author_name	Publisher_name
102	Accountancy II	Arjun Lamsal	Buddha
103	Economics	Arpita Gurung	Horizon
104	Nepali	CDC	Nepal Government

 UPDATE COMMAND: 

Update command is used to update the value from table.

SYNTAX: UPDATE table_name SET column1 = value1, column2 = value2 ……… WHERE condition;

To update the records: changing book name accountancy II from bid 102 à Social

UPDATE BOOK_INFO SET BOOK_NAME = “SOCIAL” WHERE BID = 102;

SELECT COMMANDS: 

This command is used to select the data from database. It is used to retrieve data from the database

 SELECT column1, column2, ...FROM table_name WHERE condition;

 To display all data’s, we use:

SELECT * FROM TABLE_NAME;

 EG: SELECT * FROM BOOK_DETAILS;

BID	BOOK_NAME	Author_name	Publisher_name
102	Accountancy II	Arjun Lamsal	Buddha
103	Economics	Arpita Gurung	Horizon
104	Nepali	CDC	Nepal Government

 TO DISPLAY THE REQUIRED COLUMNS

SYNTAX:SELECT COL1, COL2…. FROM TABLE_NAME;

 DISPLAY THE COLUMN BOOK ID AND BOOK_NAME

EG: SELECT BID, BOOK_NAME FROM BOOK_INFO;

BID	BOOK_NAME
102	Accountancy II
103	Economics
104	Nepali

 To add column of a table

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

SYNTAX: ALTER TABLE table_name
DROP COLUMN column_name;

DELETE A COLUMN NAME PUBLISHER_NAME FROM THE TABLE BOOK_INFO

BID	BOOK_NAME	Author_name	Publisher_name
102	Accountancy II	Arjun Lamsal	Buddha
103	Economics	Arpita Gurung	Horizon
104	Nepali	CDC	Nepal Government

Eg ALTER TABLE BOOK_INFO DROP COLUMN Publisher_name;

BID	BOOK_NAME	Author_name
102	Accountancy II	Arjun Lamsal
103	Economics	Arpita Gurung
104	Nepali	CDC

 

ALTER TABLE - ADD Column:  

To add a column we use following syntax:

SYNTAX: ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE BOOK_INFO ADD Publisher_name VARCHAR(35);

BID	BOOK_NAME	Author_name	Publisher_name
102	Accountancy II	Arjun Lamsal
103	Economics	Arpita Gurung
104	Nepali	CDC

 

ALTER TABLE - RENAME COLUMN

To rename a column in a table, use the following syntax:

SYNTAX: ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

ALTER TABLE - ALTER/MODIFY DATATYPE

ALTER TABLE Table_name MODIFY COLUMN column_name DATATYPE;

To change the data type VARCHAR(35) of a column in a table, use the following syntax:

ALTER TABLE BOOK_INFO MODIFY COLUMN Publisher_name varchar(35);