You are here: Semester >> Semester 4 >> Class Works >> DBMS Lab
Class 1
Basic Introduction about table in isqlplus(link .htaccess blocked). Equivalent to Pages Home, Intro,
Syntax, Select of w3schools.com
Syntax, Select of w3schools.com
Commands Unavailable
Class 2
Basic Introduction to datatypes used in SQL, where command, and & or, group by and order by
command.
command.
Commands Unavailable
Class 3
************ Sysdate ************
SELECT sysdate from table_name
************ Table Creation ************
CREATE TABLE table_name(Name varchar(30), Roll int primary key, age int, Country varchar(30),
Sex char(1), "Phone Number" varchar(50));
************ Values Inserted ************
INSERT INTO table_name VALUES ('When you born', 14, 23, 'India', 'M', '+91122346731');
INSERT INTO table_name VALUES ('you pink', 15, 24, 'Africa', 'F', '+91122346731');
INSERT INTO table_name VALUES ('When you grow up', 24, 17, 'America', 'M', '+91122346731');
INSERT INTO table_name VALUES ('you white', 17, 25, 'America', 'F', '+91122346731');
INSERT INTO table_name VALUES ('When you go in sun', 26, 17, 'America', 'M', '+91122346731');
INSERT INTO table_name VALUES ('you red', 19, 76, 'America', 'F', '+91122346731');
INSERT INTO table_name VALUES ('When you cold', 20, 37, 'America', 'M', '+91122346731');
INSERT INTO table_name VALUES ('you blue', 21, 57, 'America', 'M', '+91122346731');
INSERT INTO table_name VALUES ('When you scared', 22, 47, 'India', 'M', '+91122346731');
INSERT INTO table_name VALUES ('you yellow', 23, 19, 'India', 'F', '+91122346731');
INSERT INTO table_name VALUES ('When you sick', 24, 60, 'India', 'M', '+91122346731');
INSERT INTO table_name VALUES ('you green', 25, 74, 'India', 'F', '+91122346731');
INSERT INTO table_name VALUES ('And when you die', `26, 36, 'India', 'M', '+91122346731');
INSERT INTO table_name VALUES ('you gray', 27, 83, 'India', 'M', '+91122346731');
INSERT INTO table_name VALUES ('And you calling me colored ??', 28, 37, 'India', 'F', '+91122346731');
***************** To Display *****************
Q1. Retrieve number of employees living in Places
A. select Address, count(*) from table_name group by address;
Q2. Retrieve employee living in India.
A. select * from table_name where address='India';
Q3. Retrieve age of female employees
A. select age from table_name where Sex='F';
Q4. Retrieve name of Employees starting with 'W'
A. select * from table_name where name like 'W%';
Q5. Add a new column 'Salary'
A. ALTER TABLE table_name Add Salary varchar(20)
Q6. Update the 'salary' after inserting some data.
A. UPDATE table_name SET Salary='30000'
UPDATE table_name SET Salary='20044' WHERE Roll='2'
Q7. Retrieve the maximum salary of individual who does not live in Delhi
A. select max(salary) as "Highest Salary" from table_name where address != 'Delhi';
Q8. Retrieve average salary of employee
A. select avg(salary) as "Highest Salary" from table_name where address != 'Delhi';
Q9. Retrieve salary of female employees who has pay-grade above 2000
A. SELECT name,salary from table_name where salary > '2000' and sex='F'
Q10. Display phone number and name of employee who is not living in Delhi
A. SELECT "Phone Number", name from table_name where address not in ('Delhi');
Class 4
************ Table 1 ************
create table DM_CSLAB_SALES_CUSTOMER(cid int primary key, name varchar(30) NOT NULL,
address varchar(30), phone varchar(50) UNIQUE );
insert into DM_CSLAB_SALES_CUSTOMER values(2, 'live once', 'DEL', '009988776655443322');
insert into DM_CSLAB_SALES_CUSTOMER values(3, 'but if', 'DEL', '0099887766554433');
insert into DM_CSLAB_SALES_CUSTOMER values(4, 'you do', 'DEL', '00998877665544');
insert into DM_CSLAB_SALES_CUSTOMER values(5, 'it right', 'DEL', '0099887766554');
insert into DM_CSLAB_SALES_CUSTOMER values(6, 'once is', 'DEL', '009988776655');
insert into DM_CSLAB_SALES_CUSTOMER values(7, 'enough', 'DEL', '0099887765');
************ Table 1 ************
create table DM_CSLAB_SALES_Details
(
"customer ID" int references DM_CSLAB_SALES_CUSTOMER(cid),
product varchar(30),
price int check(price>100),
quantity int check(quantity>1 AND quantity<21),
Amount int
);
insert into DM_CSLAB_SALES_Details values(1, 'Dont', 20, 2, 100);
insert into DM_CSLAB_SALES_DETAILS values(2, 'Dont', 300, 3, 1100);
insert into DM_CSLAB_SALES_DETAILS values(3, 'cry', 400, 4, 2100);
insert into DM_CSLAB_SALES_DETAILS values(4, 'because', 500, 5, 3100);
insert into DM_CSLAB_SALES_DETAILS values(5, 'it', 600, 6, 4100);
insert into DM_CSLAB_SALES_DETAILS values(6, 'is', 700, 7, 1050);
insert into DM_CSLAB_SALES_DETAILS values(7, 'over', 800, 8, 1030);
insert into DM_CSLAB_SALES_DETAILS values(8, 'smile', 900, 9, 1060);
insert into DM_CSLAB_SALES_DETAILS values(9, 'because', 1000, 12, 2100);
insert into DM_CSLAB_SALES_DETAILS values(10, 'it', 1100, 13, 19000);
insert into DM_CSLAB_SALES_DETAILS values(11, 'happened', 1200, 17, 14300);
************ Retrieval ************
Q1. Display customer id from 1 table and 2nd thing from 2nd table
A. select distinct (A."customer ID"), B.Name, A.Product from DM_CSLAB_SALES_DETAILS A inner join
DM_CSLAB_SALES_CUSTOMER B on A."customer ID"=B.CID ;
Q2. Display list name, phone and product of customer who belongs to some place
A. select distinct (A."customer ID"), B.Name, A.Product, A.amount||'$' from
DM_CSLAB_SALES_DETAILS A inner join DM_CSLAB_SALES_CUSTOMER B
on A."customer ID"=B.CID ;
Q3. Display customer ID, list name, Product, and Amount where amount of product is greater than 500$
A. select distinct (A."customer ID"), B.Name,A.Product, A.amount ||'$' from
DM_CSLAB_SALES_DETAILS A inner join DM_CSLAB_SALES_CUSTOMER B on A."customer ID" = B.CID
where amount >500;
NOT YET UPDATED*
Q1. Update table with course and update it
A. ALTER TABLE DM_CSLAB_STUDENT_NAMES
ADD Course varchar(30);
update DM_CSLAB_STUDENT_NAMES
SET Course= 'BTB';
Q2. Add total and Marks and update it.
A. UPDATE DM_CSLAB_STUDENT_MARKS
SET Average = (SUB1+SUB2+SUB3)
UPDATE DM_CSLAB_STUDENT_MARKS
SET Average = (SUB1+SUB2+SUB3)/3
Q3. Add foreign key to table DM_CSLAB_STUDENT_MARKS
A. ALTER TABLE DM_CSLAB_STUDENT_MARKS
ADD FOREIGN KEY (ROLL)
REFERENCES DM_CSLAB_STUDENT_NAMES(ROLL)
Q4. Display Fname, total, average of students
A. select A.FNAME, B.TOTAL, B.Average ||'%' from
DM_CSLAB_STUDENT_NAMES A inner join DM_CSLAB_STUDENT_MARKS B on A."ROLL" = B.ROLL ;
Q5. Display question 4 with average above 70%
select A.FNAME, B.TOTAL, B.Average ||'%' from
DM_CSLAB_STUDENT_NAMES A inner join DM_CSLAB_STUDENT_MARKS B on A."ROLL" = B.ROLL
where Average >70;
OR
select A.FNAME, B.TOTAL, B.Average ||'%' as Average from
DM_CSLAB_STUDENT_NAMES A inner join DM_CSLAB_STUDENT_MARKS B on A."ROLL" = B.ROLL
where Average >70;
Q6. Display number of students course wise
SELECT count(course), COURSE FROM DM_CSLAB_STUDENT_NAMES
GROUP BY COURSE