23CS2014 – Database Management Systems Lab URK23CS1257
Ex no: 1.A
Creating and Managing Tables
Date :17/07/25
AIM:
To execute DDL commands and get the desired output.
DESCRIPTION:
DDL refers to “Data Definition Language” a subset of SQL statements that change the structure
of the database schema in some way, typically by creating, deleting, or modifying schemaobjects
such as databases, tables, and views. Most Impala DDL statements start with the keywords
CREATE ,DROP , or ALTER .
In this schema, we have four main tables: User, Event, Venue, and Ticket.
1
23CS2014 – Database Management Systems Lab URK23CS1257
Ticket table:
QUERIES AND OUTPUT:
1. Create User, Event, Venue, and ticket tables based on the given schema.
CREATE TABLE USER_URK23CS1257 (
UserID NUMBER(10),
Name VARCHAR2(255),
Email VARCHAR2(255),
Password VARCHAR2(255),
Phone VARCHAR2(20)
);
OUTPUT:
CREATE TABLE Venue_URK23CS1257 (
2
23CS2014 – Database Management Systems Lab URK23CS1257
VenueID NUMBER(10),
Name VARCHAR2(255),
Address VARCHAR2(255),
City VARCHAR2(255),
State VARCHAR2(255),
Country VARCHAR2(255));
OUTPUT:
CREATE TABLE Event_URK23CS1257 (
EventID NUMBER(10),
Name VARCHAR2(255),
EventDate DATE,
Time TIMESTAMP,
VenueID NUMBER(10),
Description VARCHAR2(500));
OUTPUT:
3
23CS2014 – Database Management Systems Lab URK23CS1257
CREATE TABLE Ticket_URK23CS1257 (
TicketID NUMBER(10),
EventID NUMBER(10),
UserID NUMBER(10),
SeatNumber VARCHAR2(20),
Price NUMBER(10, 2),
Status VARCHAR2(50)
);
OUTPUT:
2. Describe the tables.
4
23CS2014 – Database Management Systems Lab URK23CS1257
DESC USER_URK23CS1257;
DESC Event_URK23CS1257;
DESC Venue_URK23CS1257;
DESC Ticket_URK23CS1257;
5
23CS2014 – Database Management Systems Lab URK23CS1257
3. Alter the User table to add a new column Age.
ALTER TABLE USER_URK23CS1257 ADD Age NUMBER (3);
OUTPUT:
4. Drop the newly added column Age.
ALTER TABLE USER_URK23CS1257 DROP COLUMN Age;
6
23CS2014 – Database Management Systems Lab URK23CS1257
OUTPUT:
5. Rename the Venue table to Location
ALTER TABLE Venue_URK23CS1257 RENAME TO Location_URK23CS1257;
OUTPUT:
6. Modify the size of the Event table Description column to 1000.
ALTER TABLE Event_URK23CS1257 MODIFY Description VARCHAR2(1000);
7
23CS2014 – Database Management Systems Lab URK23CS1257
OUTPUT:
7. Drop the SeatNumber column from the Ticket table
ALTER TABLE Ticket_URK23CS1257 DROP COLUMN SeatNumber;
OUTPUT:
8. Add a unique constraint on the Email column in the User table
ALTER TABLE USER_URK23CS1257 ADD CONSTRAINT uq_email UNIQUE (Email);
OUTPUT:
8
23CS2014 – Database Management Systems Lab URK23CS1257
9. Rename the UserID column in the User table to ID
ALTER TABLE USER_URK23CS1257 RENAME COLUMN UserID TO ID;
OUTPUT:
10. Modify the Ticket table to add a column named “Barcode’ with a data type of
VARCHAR2(50).
ALTER TABLE Ticket_URK23CS1257 ADD Barcode VARCHAR2(50);
OUTPUT:
9
23CS2014 – Database Management Systems Lab URK23CS1257
11. Modify the Name column in the Venue table to increase its maximum length to
VARCHAR2(300)
ALTER TABLE Location_URK23CS1257 MODIFY Name VARCHAR2(300);
OUTPUT:
12. Add a foreign key constraint on the VenueID column in the Event table, referencingthe
Venue table.
ALTER TABLE Location_URK23CS1257 ADD CONSTRAINT pk_location PRIMARY KEY
(VenueID);
OUTPUT:
13. Add a CHECK constraint to check whether the UserID is between 101 and 105
OUTPUT:
10
23CS2014 – Database Management Systems Lab URK23CS1257
14. Add a unique constraint to Phone column of the User table.
ALTER TABLE USER_URK23CS1257 ADD CONSTRAINT uq_phone UNIQUE (Phone);
OUTPUT:
15. Truncate the user table.
TRUNCATE USER_URK23CS1257
OUTPUT:
RESULT: The tables were successfully created and managed using SQL commands
11
