DDL and DML Commands in SQL
DDL stands for Data Definition Language and refers to SQL commands used
to create, modify, and delete database structures such as tables, indexes,
and views. DML stands for Data Manipulation Language and refers to SQL
commands used to insert, update, and delete data within a database. Now,
let’s learn about the DDL and DML commands in depth.
What are DDL Commands in SQL?
DDL (Data Definition Language) is a type of SQL command used to define
data structures and modify data. It creates, alters, and deletes database
objects such as tables, views, indexes, and users. Examples of DDL
statements include CREATE, ALTER, DROP and TRUNCATE.
What are DML Commands in SQL?
DML (Data Manipulation Language) is a type of SQL command used to
manipulate data in a database. It inserts, updates, and deletes data from a
database table. Examples of DML statements include select,INSERT,
UPDATE, and DELETE.
Types of DDL Statements
Snega is a talented programmer who has been eager to learn more about
the various aspects of Python programming. One day, she comes across a
tutorial about databases and SQL queries. She is particularly interested in
learning about the basics of databases, such as Data Definition Language
(DDL) and Data Manipulation Language (DML). She eagerly starts reading
the tutorial. Lets help her in the journey.
1. CREATE: It is used to create objects in the database, such as tables,
views, stored procedures, and more.
2. ALTER: It is used to modify the structure of an existing database
object.
3. DROP: It is used to delete an entire object or part of an object from
the database.
4. TRUNCATE: Used to delete all records from a table but does not
delete the table structure.
5. RENAME: Used to rename an existing database object.
Types of DML Statements
1. INSERT: Used to add new records to a database table.
2. UPDATE: Used to modify existing records in a database table.
3. DELETE: Used to delete existing records from a database table.
4. MERGE: Used to combine data from two or more tables into one.
5. SELECT: Used to retrieve data from one or more tables in a
database.
6. CALL: Used to call a stored procedure or function.
DDL vs DML Commands
Explore the difference between DDL and DML commands in the below table.
Understand how DDL commands shape database structures, while DML
commands manipulate data within the database.
DDL DML
Used to define database objects like Used to manipulate data within the
tables, indexes, views, etc. database.
Examples of DDL statements include Examples of DML statements include
CREATE, ALTER, and DROP. SELECT, INSERT, UPDATE, and DELETE.
Changes made using DDL affect the Changes made using DML affect the
structure of the database. data stored in the database.
DML statements are transactional,
DDL statements are not transactional,
meaning they can be rolled back if
meaning they cannot be rolled back.
necessary.
DDL statements are usually executed by a DML statements are executed by
database administrator. application developers or end-users.
DDL statements are typically used during DML statements are used during
the design and setup phase of a database. normal operation of a database.
Examples of DDL statements: CREATE
Examples of DML statements: SELECT,
TABLE, DROP TABLE, ALTER TABLE, CREATE
INSERT, UPDATE, DELETE, etc.
INDEX, etc.
Benefits of DDL and DML
1. DDL (Data Definition Language) provides the ability to define, create
and modify database objects such as tables, views, indexes, and
users.
2. DML (Data Manipulation Language) allows for manipulating data in a
database, such as inserting, updating, and deleting records.
3. DDL and DML commands can be used to ensure data integrity in the
database.
4. DDL and DML commands provide a way to control access to the
database by granting and revoking privileges.
5. DDL and DML commands allow for the efficient retrieval of data from
the database.
6. DDL and DML commands allow for the efficient execution of queries.
7. DDL and DML commands provide a way to maintain database
performance.
IN TABULAR FORM IF DATA IS STORED THAT IS CALLED RELATION
Domain is a set of values from which an attribute can take value in each row. For
example, roll no field can have only integer values and so its domain is a set of
integer values
CARDINALITY --- NO OF ROWS IN A TABLE
DEGREE—NO OF COLUMNS IN A TABLE
EMPLOYEE--- ENTITY
Attribute in this should be unique
Candidate
key can be
single or
multiple
PRIMARY
KEY—
UNIQUE
KEY ,LIKE
EMPID
ONE
TABLE
CAN HAVE
ONLY ONE
PRIMARY
KEY
IF WE
ADD
MORE
THAN
ONE KEY
THAT IS CALLED composite KEY.
Green marked columns can be unique key
no common keys above so
DDL---DATA DEFINITION LANGUAGE
STRUCTURE OF A TABLE IS CALLED
SCHEMA
COMMANDS USED ARE :
Show , use & desc
SHOW DATABASES;
USE SCHOOL;---EXAMPLE TABLE NAME
SHOW TABLES;
DESC / DESCRIBE DEPARTMENT/SCHOOL;
THIS COMMAND SHOWS THE STRUCTURE OF A TABLE
To deal with data/ see data ----DML is used
To see the structure ---- DDL is used (Describe)
Create database school;
Use institute;
Show tables;
Create table student
(
id INT AUTO_INCREMENT PRIMARY KEY,
Regid integer,
Name varchar(30),
Gender varchar(10),
Doj date,
Course varchar(30),
Fee integer
)
show databases;
use information_schema;
show tables;
desc APPLICABLE_ROLES ;
desc ENGINES ;
-- insert
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');
-- fetch
SELECT * FROM EMPLOYEE WHERE dept = 'Sales';
Data types in sql
Date –‘yyy-mm-dd’
Logical- t/f
Difference between Char & varchar
Can be a combination of alphabets ,digits ,special symbols ,spaces
etc.
Ex: Char(20)..if we are using only 10 letters ..rest of the memory
gets wasted
Varchar(20)—return rest of the space . if 10 characters are used .
Ruchi have ---5 character length and I have given
varchar(20)..rest of the space is free.it will use only space of 5
characters .no memory is wasted.
Example
create table instructor(
name varchar(30),
age integer,
address varchar(30),
doj date,
salary float);
desc instructor;
insert into instructor values('ruchi',12,'i-16 ff','2024-12-12',1200.22);
insert into instructor values('ruchi',12,'i-16 ff','2024-12-12',1200.22);
SELECT * FROM instructor;
Constraints -- restriction imposed on attributes/ columns. Two types
1) Column level & table level
Ex: not null – means cannot be left blank
Unique – like id, phone no etc. (Cannot have duplicate values)
You can leave blank in unique. Value stored in that cannot be
duplicate
Primary key -- acts as a constraint. Can’t be left blank and can’t have
duplicate values. in table one primary key would be there. you can use
composite by joining more than one primary key.
Default – any value can be taken
Check: will take any condition
Foreign key—to establish a relationship between two tables
show databases;
use information_schema;
create table sal
(id primary key,
name varchar(30),
age integer,
dob date,
desig varchar(10),
dept varchar(20),
salary float);
insert into sal values (101,'ruchi',12,'2023-12-22',
manager,hr,22000.33)
show table sal;
select * from sal;
CREATE TABLE sal (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept varchar(10),
age integer
);
INSERT INTO sal VALUES(1001,'rr','hr',12);
select * from sal;
example
CREATE TABLE emp(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
emailid varchar(30) unique,
class varchar(20) default 'nur',
dept varchar(10) default'hr',
age integer,
address varchar(30)
);
INSERT INTO emp VALUES(1001,'rr','arpitman13@gmail.com','12','hr',12,'i16gurgram'
);
INSERT INTO emp VALUES(1003,'tr','apprrpitman13@gmail.com','lkg','hr',16,'13226 g
urugram');
CREATE TABLE sal(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
emailid varchar(30) unique,
class varchar(20) default 'nur',
dept varchar(10) default'hr',
age integer
);
INSERT INTO sal VALUES(1001,'rr','arpitman13@gmail.com','12','hr',12);
INSERT INTO sal VALUES(1002,'tr','arrpitman13@gmail.com','ukg','hr',13);
INSERT INTO sal VALUES(1003,'tr','apprrpitman13@gmail.com','lkg','hr',13);
select * from sal;
show databases;
alter table table name
add/modify/drop column name datatype
ALTER TABLE - ADD Column
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
The following SQL adds an "Email" column to the "Customers" table:
ExampleGet your own SQL Server
ALTER TABLE Customers
ADD Email varchar(255);
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):
ALTER TABLE table_name
DROP COLUMN column_name;
The following SQL deletes the "Email" column from the "Customers" table:
Example
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - RENAME COLUMN
To rename a column in a table, use the following syntax:
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
To rename a column in a table in SQL Server, use the following syntax:
SQL Server:
EXEC sp_rename 'table_name.old_name', 'new_name', 'COLUMN';
ALTER TABLE - ALTER/MODIFY DATATYPE
To change the data type of a column in a table, use the following syntax:
SQL Server / MS Access:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
My SQL / Oracle (prior version 10G):
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
ALTER TABLE changeColumnPositionDemo MODIFY
StudentAddress varchar(200) AFTER StudentAge;
Q1
Ans: 6,20
Ans:8,15
The degree of the Cartesian product is the sum of the degrees of the two tables. The cardinality
of the Cartesian product is the product of the cardinalities of the two tables
Ans: C
Ans:
Ans:
Q
Ans:
Q
Ans:
Q
Ans:
Ans 1)
Q Define the term Domain with respect to RDBMS. Give one example to support your answer.
Ans:
Domain is a set of values from which an attribute can take value in each row. For example, roll no field
can have only integer values and so its domain is a set of integer values
Note: Any other correct logic may be marked ½ mark for correct definition ½ mark for correct example
______ keyword is used to display non-repeated values in MySQL.
(a) Unique (b) Remove (c) Distinct (d) All
8. Which of the following commands will change row(s) of the table from MySQL database? (1)
(a) REPLACE TABLE (b) CHANGE TABLE (c) UPDATE (d) ALTER TABLE
_________ is a table constraint that will prevent the entry of duplicate rows.
(a) Primary Key (b) NULL (c) Unique (d) Distinct
HAVING clause is used in combination with ____________ clause.
(a) GROUP BY (b) Where (c) IN (d) Order By
Which of the following function can work with NULL values in a database? (1)
(a) avg() (b) sum() (c) count(*) (d) total(*)
16. Which command is used for counting the number of rows in a database? (1)
(a) row (b) rowcount (c) count() (d) row_count
What is the difference between ‘Primary Key’ and ‘Foreign Key’? Can a table have multiple Primary keys or
Foreign keys?
Write any two aggregate functions in SQL with an appropriate example. (2)
OR
Write two commands each of DDL and DML commands in SQL.
(b) The code given below inserts the following record in the table Fun_City:
Ticket_Id – integer
Name – string
Ticket_Price – integer
No_Of_Tickets – integer
Total_Amount – integer
Note the following to establish connectivity between Python and MySQL:
• Username is root
• Password is Ticket
• The table exists in a MySQL database named Amusement.
• The details (Ticket_Id, Name, Ticket_Price, No_of_Tickets) are to be accepted by the user.
Write the following missing statements to complete the code:
Statement 1 – To form the cursor object
Statement 2 – Write a command to execute the query
Statement 3 – Write a command to save data permanently in the database.
import mysql.connector as mysql
def sql_data():
con=mysql.connect(host="localhost",user="root",password="Ticket",
database="Amusement")
mycursor=_________________ #Statement 1
Ticket_No=int(input("Enter Ticket Number :: "))
Name=input("Enter Name :: ")
Ticket_Price= input("Enter Ticket Price :: ")
No_Of_Tickets=int(input("Enter No of tickets :: "))
Total_Amount= Ticket_Price * No_Of_Tickets
query="Insert into Fun_City values({},'{}',{},{})".format(Ticket_No, Name,
Ticket_Price, No_Of_Tickets, Total_Amount)
____________________ #Statement 2
______________________ #Statement 3
print("Data Added successfully")
