CIS340-Lab3 part 2

DDL + DML commands Example

 SQL

09141 sea pig datasi kW

of
DDL DML
DI ima sina.am
y
Create Update Delete
Alter Table Drop Table Insert Row Select
Table Row Row

Rename Rename Add Modify Drop T

a.is
Table Column Column Column Column

DataType
Hdbwi and
constrains
 7k
SQL

DDL

Create
Alter Table Drop Table
Table

create table ( Rename Rename Add Modify Drop Drop Table Table's
Table column column Column Column name;
column1
datatype
constraients,
column2
Alter Table Table's_name Alter Table Table's_name Alter Table Table's_name
datatype Alter Table Table's_name
constraients, Rename Column Alter Table Table's_name
Rename to New_Table's_ Add Column's_name datatype Modify Column's_name
Column's_name To constraients ; datatype; constraints Drop Column Coumn's_name;
...........); name; New_Column's_name

p
t w
g
 SQL

DML

Recordcell
Insert Row Update Row Delete Row Select

Update Table's_name

IoT
Insert into Table's_name values Select column1,column2, ....
Set column = value Delete from Table's_name
(colunm1's value,colunm2's From Table's_name
value, ......) ; Where column=value; Where column=value;
Where column=value;
 www.iws II II

Example
 Creating A table

Create table Passenger_Ticket

(
T_id int primary key,
passenger_name varchar(30),
passenger_age int,
flight_date date,
city varchar(50)
);
Rename a table
Q1) Rename the previous table to Ticket:

Alber

rename to
Rename a table
Q1) Rename the previous table to Ticket:
Rename a column
Q2)Rename city column to destination:
Rename a column
Q2)Rename city column to destination:
Add a new column
Q3) Add a new column named T_price (number), T_price must be greater than 500:
Add a new column
Q3) Add a new column named T_price (number), T_price must be greater than 500:
Modify a column
Q4) Modify a destination column to char(10):
Modify a column
Q4) Modify a destination column to char(10):
Modify a column
Q5) Modify a passenger_name to be not null:
Modify a column
Q5) Modify a passenger_name to be not null:
Modify a column
Another answer for Q4+Q5:
Modify a column
Another answer for Q4+Q5:
Drop a column
Q6) Drop column passenger_age:
Drop a column
Q6) Drop column passenger_age:
Drop a table
Q7) Drop Ticket table:
Drop a table
Q7) Drop Ticket table:
Inserting Rows
Insert the following rows: Note:
Oracle SQL:
Row1: (1001, 'Mona Ahmad', '03-15-2022', 'Riyadh', 750);
‘MM-DD-YYYY’
Row2: (1002, 'Amal Alnaser', '02-18-2022', 'Tabuk', 550); 03-14-2000

Row3: (1003, 'Reem Mohammed', '05-15-2022', 'Medina', 640); Live SQL:

‘DD-Mon-YYYY
14-MAR-2000
Update rows:
Q8) Update the flight_date value to ’06-23-2023’ to the passenger that her name is ‘Reem
Mohammed’
Update rows
Q8) Update the flight_date value to ’06-23-2023’ to the passenger that her name is ‘Reem
Mohammed’
Update rows
Q9) Update the destination value of the passenger who has T_id = 1002 to match the
destination of the passenger who has T_id = 1003:
Update rows
Q9) Update the destination value of the passenger who has T_id = 1002 to match the
destination of the passenger who has T_id = 1003:
Delete rows
Q10)Delete the record of the passenger that her name is ‘Mona Ahmad’:
Delete rows
Q10)Delete the record of the passenger that her name is ‘Mona Ahmad’:
Select data
Q11) show all data in Ticket table:
Select data
Q11) show all data in Ticket table: