DDL COMMANDS

 

  a) DDL COMMANDS-CREATE

     

1.      Create a table EMPLOYEE have the following fields (emp_id, name,city,dob,adhar_no)

 

1A)  CREATE DATABASE DBMS;

 CREATE TABLE EMPLOYEE(emp_id INT NOT NULL,Name VARCHAR(20),City VARCHAR(20),dob DATE,adhar_no INT);

 

  2.      Create a table STUDENT have the fields(roll_no, name, age, address, gender)and add following constraints on it.

 

 

               Roll_no – Primary key

               Name – does not allow null values

               Age- must be in between (0-99), does not allow null values

               Gender- default value is ‘m’

2A)

 CREATE TABLE STUDENT(Roll_no int Primary key,Name varchar(50) not null,Age int CHECK(Age>=0 and Age<=99),Address varchar(50),Gender varchar(10) default "M");

 DESC STUDENT;

 

 3.      Create a temporary table STUDENT1 of STUDENT relation.

 

3A)

 Create temporary table STUDENT1 select*from STUDENT;

 DESC STUDENT1;

 

4)

 Create table EMPLOYEE1 select*from EMPLOYEE;

5)

 create database INVOICE1;

 use INVOICE1;

 show tables;

 create table CUSTOMER(id INT PRIMARY KEY,Name VARCHAR(20),Address VARCHAR(20),City VARCHAR(20),Ph_no INT);

 CREATE TABLE PRODUCT(pid INT PRIMARY KEY,pName VARCHAR(20),Price int default 0);

 CREATE TABLE Invoice1_master(inv_id INT PRIMARY KEY,cust_id INT REFERENCES CUSTOMER(id),inv_date DATE);

 desc Invoice1;

 show tables;

 desc Invoice1_master;

 CREATE TABLE Invoice1_item(inv_id INT REFERENCES invoice1_master(inv_id),pid INT REFERENCES PRODUCT(id),quantity INT);

 show tables;

 

 

 

Experiment No: 1- Relational database design using MySQL -

                          (1.1) DDL Commands

RESMI S R

•

Mar 8 (Edited Mar 8)

1.1)      

 

   

 

   

    4.      Create a duplicate table of EMPLOYEE.

 

    5.        Create an INVOICE database and having following tables.

 

                      Customer(id, name, address, city, ph_no)

                      Product(pid,  pname, price (default 0))

                      Invoice_master(inv_id, cust_id(FK), inv_date)

                      Invoice_item(inv_id (FK),  pid(FK),