Question

Q.1 What is DDL, DML and DCL?

Q.2 Write syntax of Create table with primary key.

Q.3 Write syntax to insert values in the table.

Q.4 Write syntax to drop a column.

Q.5 Student(Stud_no : integer, Stud_name: string)

Membership(Mem_no: integer, Stud_no: integer)

Book(book_no: integer, book_name:string, author: string)

Iss_rec(iss_no:integer, iss_date: date, Mem_no: integer, book_no: integer)

For the above schema, perform the following—

a) Create the tables with the appropriate integrity constraints

b) Insert around 10 records in each of the tables

c) List all the student names with their membership numbers

d) List all the issues for the current date with student and Book names

e) List the details of students who borrowed book whose author is CJDATE

Answer 1

A1)

• DDL - Data Definition Language refers to those commands that are used to define/create data. Their commands include CREATE, ALTER, DROP, etc.
• DML - Data Manipulation Language refers to those commands that are used to manipulate data/records. Their commands include INSERT, UPDATE, DELETE, etc.
• DCL - Data Control Language refers to those commands that deal with rights/permissions of the system. Their commands include GRANT and REVOKE.

A2) CREATE TABLE <table_name>(<column_name1> <datatype> Primary Key, <column_name2> <datatype> <constraint2>, <column_name3> <datatype> <constraint3> ... );

A3) INSERT INTO <table_name> VALUES(<value1>, <value2>, <value3>, <value4>, ...);

A4) ALTER <table_name> DROP <column_name>;

A5)

a)

• CREATE TABLE Student(Stud_no int Primary Key, Stud_name v‎archa‎r(30));
• CREATE TABLE Membership(Mem_no int Primary Key, Stud_no int Primary Key);
• CREATE TABLE Book(Book_no int Primary Key, Book_name va‎rch‎ar(40), Author v‎archa‎r(40));
• CREATE TABLE Iss_rec(Iss_no int Primary Key, Iss_date date, Mem_no int, Book_no int Primary Key);

b)

• INSERT INTO Student VALUES(4, 'Vanessa');
• INSERT INTO Student VALUES(5, 'April');
• INSERT INTO Student VALUES(6, 'Jeanette');
• INSERT INTO Student VALUES(7, 'Veronica');
• INSERT INTO Student VALUES(10, 'Dylan');
• INSERT INTO Student VALUES(15, 'Zedd');
• INSERT INTO Student VALUES(16, 'David');
• INSERT INTO Student VALUES(17, 'Grayson');
• INSERT INTO Student VALUES(8, 'Soulene');
• INSERT INTO Student VALUES(19, 'Bellamy');

• INSERT INTO Membership VALUES(001, 4);
• INSERT INTO Membership VALUES(002, 6);
• INSERT INTO Membership VALUES(005, 5);
• INSERT INTO Membership VALUES(041, 17);
• INSERT INTO Membership VALUES(412, 19);
• INSERT INTO Membership VALUES(070, 16);
• INSERT INTO Membership VALUES(510, 10);
• INSERT INTO Membership VALUES(007, 8);
• INSERT INTO Membership VALUES(012, 15);
• INSERT INTO Membership VALUES(516, 7);

• INSERT INTO Book VALUES(21, 'IT', 'Stephen King');
• INSERT INTO Book VALUES(22, 'Magnus Chase and the Hammer of Thor', 'Rick Riordan');
• INSERT INTO Book VALUES(23, 'Divergent', 'Veronica Roth');
• INSERT INTO Book VALUES(24, 'Insurgent',' Veronica Roth');
• INSERT INTO Book VALUES(25, 'Crazy Is My Superpower', 'April Jeanette Mendez Brooks);
• INSERT INTO Book VALUES(26, 'Four', 'Veronica Roth');
• INSERT INTO Book VALUES(27, 'The Hidden Oracle: The Trials of Apollo', 'Rick Riordan');
• INSERT INTO Book Values(28, 'Mortal Engines', 'Philip Reeve');
• INSERT INTO Book Values(29, 'The Hunger Games: Mockingjay', 'Suzanne Collins');
• INSERT INTO Book VALUES(30, 'Percy Jackson & the Olympians', 'Rick Riordan');

• INSERT INTO Iss_rec(089, '2001-12-01', 515, 4);
• INSERT INTO Iss_rec(212, '2004-10-13', 66, 7);
• INSERT INTO Iss_rec(455, '2004-04-02', 8, 79);
• INSERT INTO Iss_rec(41, '2015-10-07', 78, 72);
• INSERT INTO Iss_rec(900, '2011-03-16', 89, 88);
• INSERT INTO Iss_rec(400, '2014-02-10', 87, 6);
• INSERT INTO Iss_rec(79, '1998-09-03', 46, 99);
• INSERT INTO Iss_rec(623, '2017-05-05', 95, 12);
• INSERT INTO Iss_rec(45, '2017-10-11', 478,23);
• INSERT INTO Iss_rec(89, '2012-03-26', 56, 9);

c) Select Stud_name, Mem_no from Student, Membership where Student.Stud_no = Membership.Mem_no;

d) Select Iss_no, Stud_name, Book_name from Iss_rec, Student, Book where Iss_rec.Mem_no = Membership.Mem_no and Membership.Stud_no = Student.Student_no;

e) Select * from Student where Stud_no in (Select Stud_no from Membership where Mem_no in (Select Mem_no from Iss_rec where Book_no in (Select Book_no from Book where Author = 'CJDATE')));