Computer Tutorial

Database Schema for a Video Library scenario Customer(cust_no: integer,cust_name: string) Membership( Mem_no: integer , cust _no: integer ) Cassette( cass_no:integer , cass_name:string, Language: String) Iss_rec (iss_no: integer , iss_date: date, mem_no: integer , cass_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 customer names with their membership numbers d)    List all the issues for the current date with the customer names and cassette names e)    List the details of the customer who has borrowed the cassette whose title is “ The Legend” f)     Give a count of how many cassettes have been borrowed by each customer g)    Give a list of book which has been taken by the student with mem_no as 5 h)    List the cassettes issues for today i)     Create a view which lists outs the iss_no, iss_date, c

Different types of commands in SQL: A). DDL commands: - To create a database objects B).         DML commands: - To manipulate data of a database objects C).         DQL command: - To retrieve the data from a database. D).         DCL/DTL commands: - To control the data of a database… DDL commands: 1. The Create Table Command: - it defines each column of the table uniquely. Each column has minimum of three attributes, a name , data type and size. Syntax: Create table <table name> (<col1> <datatype>(<size>),<col2> <datatype><size>)); Ex:     create table emp(empno number(4) primary key, ename char(10)); 2. Modifying the structure of tables. a)add new columns Syntax: Alter table <tablename> add(<new col><datatype(size),<new col>datatype(size)); Ex: alter table emp add(sal number(7,2)); 3. Dropping a column from a table. Syntax: Alter table <tablename>