(Grade X) Lab Assignment
Set A
a.
Create a database named employee.
CREATE DATABASE employee;
b.
Create a table employee with the given fields.
CREATE TABLE employee (
employee_id INT,
employee_name VARCHAR(100),
employee_address
VARCHAR(100),
employee_salary FLOAT
);
c.
Insert any 6 records.
INSERT INTO employee VALUES
(1, 'Amit Sharma', 'Kathmandu', 30000),
(2, 'Bikash Rai', 'Pokhara', 20000),
(3, 'Anita Gurung', 'Lalitpur', 25000),
(4, 'Ramesh Y', 'Kathmandu', 20000),
(5, 'Sita Ary', 'Bhaktapur', 18000),
(6, 'Ajay', 'Kathmandu', 30000);
d.
Display all the records of all fields.
SELECT * FROM employee;
e.
Display employee_id and employee_name.
SELECT employee_id, employee_name
FROM employee;
f.
Display employees whose name starts with 'A'.
SELECT * FROM employee
WHERE employee_name LIKE 'A%';
g.
Display employees whose name ends with 'Y'.
SELECT * FROM employee
WHERE employee_name LIKE '%Y';
h.
Display employees whose salary is 20000.
SELECT * FROM employee
WHERE employee_salary = 20000;
i.
Display employees whose salary is 30000 and address is 'Kathmandu'.
SELECT * FROM employee
WHERE employee_salary = 30000
AND employee_address = 'Kathmandu';
j.
Update the employee name with any other name who has id 1.
UPDATE employee
SET employee_name = 'Rajesh Sharma'
WHERE employee_id = 1;
k.
Delete the employee whose id is 3.
DELETE FROM employee
WHERE employee_id = 3;
l.
Display records sorted by employee_name.
SELECT * FROM employee
ORDER BY employee_name ASC;
m.
Add employee_department field.
ALTER TABLE employee
ADD employee_department VARCHAR(100);
Set B
a.
Create a database named staff.
CREATE DATABASE staff;
b.
Create a table staff.
CREATE TABLE staff (
staff_id INT,
staff_name VARCHAR(100),
staff_department VARCHAR(100),
staff_bonus FLOAT
);
c.
Insert any 5 records.
INSERT INTO staff VALUES
(1, 'Suman', 'IT', 2000),
(2, 'Rita', 'HR', 1500),
(3, 'Santosh', 'Finance', 2500),
(4, 'Sagar', 'IT', 2000),
(5, 'Mina', 'Library', 1800);
d.
Display all records.
SELECT * FROM staff;
e.
Display staff_id and staff_department.
SELECT staff_id, staff_department
FROM staff;
f.
Display staff whose name starts with 'S'.
SELECT * FROM staff
WHERE staff_name LIKE 'S%';
g.
Display staff whose department ends with 'Y'.
SELECT * FROM staff
WHERE staff_department LIKE '%Y';
h.
Display staff whose bonus is 2000.
SELECT * FROM staff
WHERE staff_bonus = 2000;
i.
Display staff whose bonus is 2000 and department is 'IT'.
SELECT * FROM staff
WHERE staff_bonus = 2000
AND staff_department = 'IT';
j.
Update the staff name whose id is 4.
UPDATE staff
SET staff_name = 'Rajan'
WHERE staff_id = 4;
k.
Delete the staff whose id is 5.
DELETE FROM staff
WHERE staff_id = 5;
l.
Display records sorted by staff_name.
SELECT * FROM staff
ORDER BY staff_name ASC;
m.
Add staff_position field.
ALTER TABLE staff
ADD staff_position VARCHAR(100);
Set C
a.
Create a database named library.
CREATE DATABASE library;
b.
Create a table book.
CREATE TABLE book (
book_id INT,
book_title VARCHAR(100),
book_author VARCHAR(100),
book_price
FLOAT
);
c.
Insert any 5 records.
INSERT INTO book VALUES
(1, 'The Alchemist', 'Paulo Coelho', 500),
(2, 'Thinking Fast', 'Ram Sharma', 350),
(3, 'Tomorrow', 'Hari Kiran', 450),
(4, 'Python Basics', 'Suman', 250),
(5, 'Technology', 'Ram Sharma', 600);
d.
Display all records.
SELECT * FROM book;
e.
Display book_id and book_title.
SELECT book_id, book_title
FROM book;
f.
Display books whose title starts with 'T'.
SELECT * FROM book
WHERE book_title LIKE 'T%';
g.
Display books whose author name ends with 'N'.
SELECT * FROM book
WHERE book_author LIKE '%N';
h.
Display books whose price is 500.
SELECT * FROM book
WHERE book_price = 500;
i.
Display books whose price is greater than 300 and author is 'Ram Sharma'.
SELECT * FROM book
WHERE book_price > 300
AND book_author = 'Ram Sharma';
j.
Update the book title whose id is 2.
UPDATE book
SET book_title = 'Database Concepts'
WHERE book_id = 2;
k.
Delete the book whose id is 4.
DELETE FROM book
WHERE book_id = 4;
l.
Display records sorted by book_title.
SELECT * FROM book
ORDER BY book_title ASC;
m.
Add book_genre field.
ALTER TABLE book
ADD book_genre VARCHAR(100);
Set D
a.
Create a database named hospital.
CREATE DATABASE hospital;
b.
Create a table patient.
CREATE TABLE patient (
patient_id INT,
patient_name VARCHAR(100),
patient_disease VARCHAR(100),
patient_age INT
);
c.
Insert any 5 records.
INSERT INTO patient VALUES
(1, 'Sita', 'Diabetes', 45),
(2, 'Ram', 'Asthma', 30),
(3, 'Shyam', 'Malaria', 28),
(4, 'Sarita', 'Diabetes', 50),
(5, 'Hari', 'Influenza', 35);
d.
Display all records.
SELECT * FROM patient;
e.
Display patient_id and patient_name.
SELECT patient_id, patient_name
FROM patient;
f.
Display patients whose name starts with 'S'.
SELECT * FROM patient
WHERE patient_name LIKE 'S%';
g.
Display patients whose disease ends with 'A'.
SELECT * FROM patient
WHERE patient_disease LIKE '%A';
h.
Display patients whose age is 30.
SELECT * FROM patient
WHERE patient_age = 30;
i.
Display patients whose age is greater than 40 and disease is 'Diabetes'.
SELECT * FROM patient
WHERE patient_age > 40
AND patient_disease = 'Diabetes';
j.
Update the patient name whose id is 3.
UPDATE patient
SET patient_name = 'Rohan'
WHERE patient_id = 3;
k.
Delete the patient whose id is 2.
DELETE FROM patient
WHERE patient_id = 2;
l.
Display records sorted by patient_name.
SELECT * FROM patient
ORDER BY patient_name ASC;
m.
Add patient_address field.
ALTER TABLE patient
ADD patient_address VARCHAR(100);