8/8/2019 Amity School of Engineering and Technology

1/11

8/8/2019 Amity School of Engineering and Technology

2/11

The Data Definition Language (DDL) is used to create and

destroy databases and database objects. These commands

will primarily be used by database administrators during

the setup and removal phases of a database project. Let's

take a look at the structure and usage of four basic DDL

commands:

Structure and Usage of some common DDL commands is:

1. CREATE TABLE

This DDL command is used to create a new table into

any existing database.

The syntax of this command is as follows:

Create table (column_name datatype

size constraints, column_name datatype size

constraints, column_name datatype size constraints,

.);

2.ALTER TABLE

This DDL command is used to add / modify a column

in any existing table of a database.

The syntax of this command is as follows:

Alter table ADD/MODIFY (column_name

datatype size constraints, column_name datatype size

constraints, column_name datatype size constraints,

);

3.DROP TABLE

8/8/2019 Amity School of Engineering and Technology

3/11

This DDL command is used to remove a table from any

existing database.

The syntax of this command is as follows:

Drop table < table name>

8/8/2019 Amity School of Engineering and Technology

4/11

DDL Commands Examples

1. CREATE TABLE customer (first_name char(50),

last_name char(50), address char(50),city char(50),

country char(50), birth_date date);

2. CREATE TABLE employee (fname varchar(15), lname

varchar(15), ssn char(9), empid number, salarynumber);

3. CREATE TABLE department (dname varchar(15),

dnumber number, mgr_ssn char(9), mgr_start_date

date);

4. CREATE TABLE dept_locations (dnumber number,

dlocation varchar(15));

5. CREATE TABLE project (pname varchar(15), pnumber

number, plocation varchar(15), dnum number);

6. CREATE TABLE works_on (essn char(9), pno number,

hours number);

7. CREATE TABLE dependent (essn char(9),

dependent_name varchar(15), sex char, birth_datedate, relationship varchar(9));

8/8/2019 Amity School of Engineering and Technology

5/11

8. ALTER TABLE employee ADD experience number(3);

9. ALTER TABLE employee DROP location;

10. ALTER TABLE employee MODIFY salary number(15,2);

11. ALTER TABLE Person ADD City varchar(30);

12. ALTER TABLE Person DROP COLUMN Address;

13. ALTER TABLE orders ADD discount varchar(10);

14. DROP TABLE employee;

15. DROP TABLE dependent;

8/8/2019 Amity School of Engineering and Technology

6/11

Data Manipulation Language

The Data Manipulation Language (DML) is used to retrieve,

insert and modify database information. These commands

will be used by all database users during the routine

operation of the database.

Structure and usage of some DML commands are as

follows:

1.INSERT

The INSERT command in SQL is used to add records to

an existing table.

The syntax of this command is as follows:

INSERT into values (value1, value2,

value3, ................., value N );

2.SELECT

8/8/2019 Amity School of Engineering and Technology

7/11

This DML command is used to retrieve information

from any table (s) to display/ list/ report on the screen,

but this command cannot any how update/ modify anytable.

The syntax of this command is as follows:

Select [*/column list] from [where

] [order by [asc /desc ]] [

group by ]

3.UPDATE

Once there's data in the table, we might find that

there is a need to modify the data. To do so, we can

use the UPDATE command.

The syntax of this command is as follows:

UPDATE SET = [new

value WHERE [condition];

4.DELETE

This command deletes all the rows from the table that

match the WHERE clause.

8/8/2019 Amity School of Engineering and Technology

8/11

The syntax of this command is as follows:

DELETE FROM WHERE

DML Commands Examples

1. INSERT INTO Persons VALUES (4,'Nilsen', 'Johan','Bakken', 'Stavanger');

2. INSERT INTO Persons (P_Id, Last_Name, First_Name)

VALUES (5, 'Tjessem', 'Jakob');

3. INSERT INTO Store_Information (store_name, Sales,Date) VALUES ('Los Angeles', 900, 'Jan-10-1999');

4. INSERT INTO suppliers (supplier_id, supplier_name)VALUES (24553, 'IBM');

8/8/2019 Amity School of Engineering and Technology

9/11

5. SELECT city, state FROM towntable WHEREpopulation > '100000';

6. Select * from employee;

7. SELECT store_name FROM Store_Information;

8. SELECT name FROM customers WHERE city='Rome';

9. UPDATE citylist SET population = population+1

WHERE name = 'Argos' and state = 'Indiana';

10. UPDATE Persons SET Address='Nissestien 67',City='Sandnes' WHERE LastName='Tjessem' ANDFirstName='Jakob';

11. UPDATE Persons SET Address='Nissestien 67',City='Sandnes';

12. UPDATE Store_Information SET Sales = 500 WHEREstore_name = "Los Angeles" AND Date = "Jan-08-1999";

13. DELETE FROM suppliers WHERE supplier_name ='IBM';

14. DELETE FROM citylist WHERE name = 'Argos' andstate = 'Indiana';

15. DELETE FROM Store_Information WHERE store_name= "Los Angeles";

8/8/2019 Amity School of Engineering and Technology

10/11

Data Control Language

Data Control Language or DCL is a part Structured Query

Language (SQL) used to control the access to data in adatabase.

Data Control Language primarily includes the followingcommands:

1. GRANT: To grant users the right to access thedatabase or perform certain tasks.

The syntax of this command is as follows:

8/8/2019 Amity School of Engineering and Technology

11/11

GRANT [all/specific permissions] ON [tablename] TO

[user account];

2. REVOKE: To cancel any previously granted or denied

permission.

The syntax of this command is as follows:

REVOKE [all/specific permissions] ON [tablename]

FROM [user account];

DCL Commands Examples

1. GRANT CREATE TABLE TO user1;

2. REVOKE CREATE TABLE FROM user1;

3. GRANT SELECT ON TABLE MyTable TO user1;

4. REVOKE SELECT ON TABLE MyTable FROM user1;

5. GRANT SELECT ON s,sp TO PUBLIC;