Week-1: CREATION OF TABLES
CREATION OF DATABASE:
Syntax:
Mysql>create database databasename;
To check the database present in the Mysql:
Syntax: show databases;
To work on particular database
Syntax:use databasename;
To check whether tables are present or not:
Syntax:show tables;
Exp 1:
Aim: To create a table called Employee with the following Structure and Answer the
following queries.
Name Type
Empno INTEGER
Ename Varchar(20)
Job Varchar(20)
Mgr INTEGER
Sal INTEGER
Sql>create table Employee (Empno INTEGER ,Ename VARCHAR(20),job VARCHAR(20),
Mgr INTEGER,Sal INTEGER);
Sql>createtable Employee (EmpnoINTEGER,Ename VARCHAR(20),job VARCHAR(20),
MgrINTEGER,Sal INTEGER); constraintpk_employeesprimarykey (empno),
constraintfk_employees_deptnoforeignkey (deptno) references DEPARTMENTS (deptno));
Sql> Select * from Employee;
Output:
a. Add a column commission with domain to the Employee
table Sql> Alter table employee add commission INTEGER;
Output:
b. Insert any five records in to the table.
Sql> INSERT INTO Employee VALUES (1, 'King', 'ITmanager', '100', '20000' );
Sql> INSERT INTO Employee VALUES (5, 'blake', 'IT', '200', '30000' );
Sql> INSERT INTO Employee VALUES (9, 'raj', 'manager', '300', '40000' );
Sql> INSERT INTO Employee VALUES (19, 'clarke', 'Assistant', '400', '50000' );
Sql> INSERT INTO Employee VALUES (25, 'mohan', 'clerk', '500', '60000' );
Output:
c. Update the column details of job
Sql> UPDATE EMPLOYEE SET JOB = 'MANAGER'WHERE JOB IS NULL;
Output:
d.Rename the column of Employ table using alter command.
Sql>ALTER TABLE Employee RENAME COLUMN Ename TO Employname;
Output:
e.Delete the employee whose empno is19.
Sql>DELETE from employee a WHERE a.empno = '19';
Output:
d.
Exp 2:
Aim: Create department table with the following structure and answer the following quries.
Name Type
Deptno Integer
Deptname Varchar(20)
location Varchar(20)
Sql> create table dept(Deptno integer,Deptname varchar(20),location varchar(20) ,constraint
pk_dept primary key(deptno));
Output:
a. Add column designation to the department
table. Sql>Alter table dept add designation
Varchar(20);
Output:
b. Insert values into the table.
Sql> insert into dept values(101, ‘cse’,’nellore’,’assistant’);
Sql> insert into dept values(102, ‘Ece’, ‘tpty’, ‘assistant’);
Sql> insert into dept values(103, ‘eee’, ‘banglore’, ‘HR’);
Sql> insert into dept values(104, ‘civil’, ‘Hyd’, ‘manager’);
Sql> insert into dept values(101, ‘cse’, ‘ chittoor’, ‘assistant’);
Output:
c. List the records of emp table grouped by dept no
sql>SELECT empno from employee, dept GROUP BY
deptno; Output:
d.
D.Update the record where dept no is9.
Sql> UPDATE DEPT SET DEPTNO=9 WHERE
LOCATION='TPTY';
Output:
e. Delete any column data from the
table Sql> ALTER TABLE DEPT
DROP COLUMN LOCATION;
Exp 3:
Aim: To create a table called Customer table and answer the following queries.
Name Name Type
Custname Varchar(20)
custstreet Varchar(20)
custcity Varchar(20)
Sql>CREATE TABLE customer ( custname Varchar(20), custstreet Varchar(20),
custcity Varchar(20));
a. Insert records into the table
Sql>INSERT INTO
CUSTOMER(CUSTNAME,CUSTSTREET,CUSTCITY)
-> VALUES('kumar', '4street', 'hyd'),
-> ('vasu', 'marthali', 'Banglore'),
-> ('hari', 'Siliconcity','Bang');
Output:
b. Add salary column to the table
Sql> Alter table dept add salary integer;
Output:
c.Alter the table column domain.
Sql>
> UPDATE DEPT SET custname=’cname’;
ALTER TABLE dept
RENAME COLUMN custname TO cname;
Output:
c. Drop salary column of the customer table.
Sql> Alter table customer drop column
salary; Output:
d. Delete the rows of customer table whose ust_city is
„hyd‟. Sql>DELETE FROM customer WHERE custcity
=”hyd‟; Output:
e.
f.Create a table called branch table.
Name Name Type
branchname Varchar(20)
Branch Varchar(20)
asserts Varchar(20)
Sql> Create table branch ( branchname Varchar(20), Branch Varchar(20), asserts Varchar(20);
Output:
Exp 4:
Aim: To increase the size of data type for asserts to the branch and
answer the following queries
a) Add and drop a column to the
branch table. Sql> Alter table
branch add branchid Integer;
Output:
Sql> Alter table branch drop column
branchid; Output:
b) Insert values to the table.
Sql> insert into branch values („kukatpally‟,
„Iron‟, „Iron_rods‟); Sql> insert into branch values
(„amerpet‟, „steel‟, „steel_plates‟); Sql> insert into
branch values („SRNagar‟, „soap‟, „soapplant‟);
Output:
c) Update the branch name column
Sql>ALTER TABLE dept
RENAME COLUMN branchname TO bname;
Output:
d) Delete any two columns from the table
Sql> Alter table branch drop
column bname,drop column
asserts;
Output:
Exp 5:
Aim: Create a table called sailor table and answer the following queries
Sailors(sid: integer, sname: string, rating: integer, age: real);
SQL>CREATE TABLE sailors ( sid integer not null,sname
varchar(32),rating integer,CONSTRAINT PK_sailors
PRIMARY KEY (sid) );
a.Add column age to the
sailortable. Sql> alter
table sailors add column
age int; Output:
b. Insert values into the sailortable.
Sql> INSERT INTO sailors ( sid, sname, rating, age ) VALUES ( 22,
'Dustin', 7, 45.0 ); Sql> INSERT INTO sailors ( sid, sname, rating,
age ) VALUES ( 23, 'brutes', 9, .60.0 ); Sql> INSERT INTO sailors (
sid, sname, rating, age ) VALUES ( 24, 'luber', 8, 58.0 );
c. Delete the row with rating>8.
Sql> delete from sailors
where ratting>8; Output:
d. Update the column details ofsailor.
Sql> Alter table sailors rename
sname to “sailorname”; Output:
e. Insert null values into thetable.
Sql> INSERT INTO sailors ( sid, sname, rating, age ) VALUES ( 26,
'Dustin', , 45.0 ); Sql> INSERT INTO sailors ( sid, sname, rating, age )
VALUES ( 22, ' ', 7, 45.0 ); Output:
Exp 6:
Aim: To Create a table called reserves table and answer the following queries
Reserves(sid: integer, bid: integer, day: date)
Sql> CREATE TABLE reserves ( sid integer not null, bid integer not null, day
datetime not null, CONSTRAINT PK_reserves PRIMARY KEY (sid, bid, day),
FOREIGN KEY (sid) REFERENCES sailors(sid), FOREIGN KEY (bid) REFERENCES
boats(bid) );
a. Insert values into the reserves table.
Sql> INSERT INTO reserves ( sid, bid, day ) VALUES ( 22,
101, '1998-10-10'); Sql> INSERT INTO reserves ( sid, bid,
day ) VALUES ( 23, 101, '1998-10-10'); Sql> INSERT INTO
reserves ( sid, bid, day ) VALUES ( 24, 102, '1998-10-09');
Sql> INSERT INTO reserves ( sid, bid, day ) VALUES ( 25,
102, '1998-10-08'); Output:
b. Add column time to the reserves table.
Sql> Alter table reserves add column
bname Varchar(20); Output:
c. Alter the column day
data type to date. Sql>
Alter table reserves
modify day date; Output:
d. Drop the column time in the table.
Sql> Alter table reserves drop column day;
Output:
e. Delete the row of the table with
some condition. Sql> Delete from
reserves where sid=’101’;
Output:
