Scribd
Upload
57 views6 pages

Rdbms Lab-5: Jenma Maria Binoy Rollno 34

This document provides the SQL commands to create tables with various constraints, alter tables by adding, modifying, and dropping columns, and rename and truncate tables. It includes commands to: 1. Create five tables - employee, student, company, product, and payroll - with different constraints like primary keys and foreign keys. 2. Alter the employeee table by adding columns for age, salary, dept_no, and modifying the designation column. It also drops the salary and dept_no columns. 3. Rename the employeee table to emp, truncate the data from emp, and drop the emp table.

Uploaded by

Jenma Maria Binoy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
57 views6 pages

Rdbms Lab-5: Jenma Maria Binoy Rollno 34

This document provides the SQL commands to create tables with various constraints, alter tables by adding, modifying, and dropping columns, and rename and truncate tables. It includes commands to: 1. Create five tables - employee, student, company, product, and payroll - with different constraints like primary keys and foreign keys. 2. Alter the employeee table by adding columns for age, salary, dept_no, and modifying the designation column. It also drops the salary and dept_no columns. 3. Rename the employeee table to emp, truncate the data from emp, and drop the emp table.

Uploaded by

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

RDBMS LAB-5

Jenma Maria Binoy


ROLLNO 34
Experiment No: 5
AIM
Familiarization of SQL.
 Create the following Tables & generate queries and verify the output for the following questions:

1. DDL command to add constraints:


i. Create a table employee with empid, name, dept and salary and make empid
as the primary key.
SQL> create table employee(empid number(3) primary key, name
varchar(10),dept varchar(12),salary number(8,2));

ii. Make the field roll_no of the table student as the primary key.
SQL> create table student(roll_no number(3) primary key, name
varchar(10));

iii. Make the field company_id of the company table as primary key.
SQL> create table company(company_id number(4) primary
key,company_name varchar(12));

iv. Create a table product with pdt_id , pdt_name, price and emp_id as field and set emp_id as
the foreign key.
create table product(pdt_id number(3),pdt_name varchar(6),price number(6,2), emp_id
references employee(empid));
v. Create a table payroll with fields empno, empname, salary, total_salary, and check whether
total_salary is between 10,000 and 20,000.

SQL> create table payroll(empno number(3),empname varchar(12),salary number(6,2) ,


total_salary number(8), check(total_salary between 10000 and 20000));

2. DDL command to ALTER, DROP, and TRUNCATE:


a) Create a table employee with following fields emp_no , emp_name , and designation.
SQL> create table employeee(emp_no number(3),emp_name varchar(12), designation
varchar(12));

b) Add a new column age into the table.


SQL> alter table employeee add age number(2);

c) Add columns salary and dept_no into the table employee.


SQL> alter table employeee add(salary number(8),dept_no number(3));
d) Modify the existing designation column with varchar to char.
SQL> alter table employeee modify designation char(12);

e) Drop the existing column salary.


SQL> alter table employeee drop column salary;

f) Drop column dept_no.


SQL> alter table employeee drop column dept_no;
g) Add columns dept_no and dept_name
SQL> alter table employeee add(dept_no number(8),dept_name varchar(13));

h) Rename the table employee to emp


SQL> alter table employeee rename to emp;

i) Drop and truncate emp


SQL> truncate table emp;
SQL> drop table emp;

You might also like