Scribd
Upload
11 views16 pages

Week 1

The document outlines various SQL operations for creating and managing databases and tables, including creating a database, creating tables for Employee, Department, Customer, Branch, Sailors, and Reserves, and performing operations such as inserting, updating, and deleting records. It provides specific SQL commands for each operation, along with examples and outputs for clarity. The document serves as a practical guide for executing basic SQL commands and managing relational database structures.

Uploaded by

nalapatlanimithagoud3
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
11 views16 pages

Week 1

The document outlines various SQL operations for creating and managing databases and tables, including creating a database, creating tables for Employee, Department, Customer, Branch, Sailors, and Reserves, and performing operations such as inserting, updating, and deleting records. It provides specific SQL commands for each operation, along with examples and outputs for clarity. The document serves as a practical guide for executing basic SQL commands and managing relational database structures.

Uploaded by

nalapatlanimithagoud3
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 16

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:

You might also like