1. Search "XAMPP Control Panel". Open it
2. Start MySQL and click to SHELL
3. Type mysql -u root and press enter key
Q2) Go inside the database "Library". Ans: USE Library
Q3) Create a table named "book".
Ans:
Create table book
( bookid int primary key,
bookname varchar(100),
price float );
After creating table, following code will be used to insert data into the corresponding table:
insert into book
(bookid, bookname, price)
values
(101,"Muna Madan",25.25) ,
(102,"Ramayan",1300.95) ,
(103,"Gauri",45.25) ,
(104,"Half Girlfriend",670.23) ,
(105,"2 states",245.75) ;
Q4) Display all records of book. Ans: SELECT * FROM book;
Q5) Display bookname and price only from table book. Ans: SELECT bookname,price FROM book;
Q6) Add a column bookauthor after bookname. Ans: alter table book add bookauthor varchar(100) after bookname;
Q7) Update the book author name of id 105 in the table book Ans: Update book SET bookauthor="Chetan Bhagat" Where bookid=105;
Q8) Update multiple author name at one time in the table book UPDATE book SET bookauthor = CASE bookid WHEN 101 THEN 'L. P. Devkota' WHEN 102 THEN 'Valmiki' WHEN 103 THEN 'Madhav Ghimire' WHEN 104 THEN 'Chetan Bhagat' END WHERE bookid IN (101, 102, 103,104);
Q9) Display the record of bookname which start with letter "M". Ans:select * from book where bookname like "M%";
Q10) Display the record of bookname which start with letter "H". Ans:select * from book where bookname like "H%";
Q11) Display the record of bookname which end with letter "n". Ans:select * from book where bookname like "%n";
Q12) Display the record of bookname with second letter "a". Ans:select * from book where bookname like "_a%";
Q13) Display the bookname and price which start with letter "M". Ans:select bookname,price from book where bookname like "M%";
Q14) Update the bookauthor name from L.P. Devkota to Laxmi Prasad Devkota. Ans:update book set bookauthor = "Laxmi Prasad Devkota" Where bookid=101;
Q15) Display the maximum price of book. Ans:select max(price) from book;
Q16) Count and display the total records of table book. Ans:select count(*) from book;
Q17) Display the lowest price book details. Ans:select bookid,bookname,bookauthor,min(price) from book ;
Q18) Display the average price of book . Ans:select avg(price) from book;
Q19) Display the sum of all bookprice. Ans:select sum(price) from book;
Q20) Delete the record of bookid 105. Ans:delete from book where bookid=105;
Q21) Delete all records of table book. Ans:delete from book;
Write SQL query to:
(a) Create the above table with primary key "Cust_id".
Ans:CREATE TABLE customer
(
cust_id VARCHAR(4) PRIMARY KEY,
cust_name VARCHAR(100),
address VARCHAR(100),
contact_no VARCHAR(10)
);
(b) Insert the given set of data in the table.
Ans:
INSERT INTO customer
(cust_id, cust_name, address, contact_no)
VALUES
("C005", "Rakesh", "Birgunj", "9852011111"),
("C010", "Mahesh", "Nepalgunj", "9862022222"),
("C012", "Aniket", "Butwal", "9805033333"),
("C020", "Hitesh", "Pokhara", "9812344444"),
("C025", "Mandip", "Birgunj", "9842055555"),
("C028", "Hari", "Pokhara", "9842166666");
(c) Display Customer Names and contact numbers whose adress starts with 'B'.
Ans: SELECT cust_name, contact_no FROM customer
WHERE address LIKE "B%";
Also consider the data for the above tables as follows:
Write SQL query to:
a) Write the SQL statement to create the 'employee' table by using data type, size and constraint. [3]
Ans: CREATE TABLE employee (
EmpNo VARCHAR(6) PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(20) NOT NULL,
Department VARCHAR(10) NOT NULL,
Allowance NUMERIC(18,2) NOT NULL,
BasicSalary NUMERIC(18,2) NOT NULL
);
INSERT INTO employee (EmpNo, Name, Address, Department, Allowance, BasicSalary) VALUES
('E001', 'Deepak Tiwari', 'Pokhara', 'Account', 2700, 30000),
('E002', 'Ram Gupta', 'Birgunj', 'HR', 3200, 25000),
('E003', 'Rohan Kumar', 'Bharatpur', 'IT', 2400, 40000),
('E004', 'Binod Sharma', 'Kathmandu', 'Manager', 3700, 60000),
('E005', 'Maya G.C.', 'Butwal', 'Account', 5200, 32000),
('E006', 'Santa Amgain', 'Dharan', 'Marketing', 2000, 27000),
('E007', 'Sabita Tiwari', 'Gorakha', 'Sales', 3000, 18000),
('E008', 'Sulav Adhikari', 'Siraha', 'HRM', 2000, 32000);
b) Display the employee table. [2]
Ans: SELECT * FROM employee;
c) Write a SQL statement to the list the employee whose salary is more than 20,000. [2]
Ans: SELECT *
FROM employee
WHERE BasicSalary > 20000;
d) Write SQL statement to list the employee who is working in the Account department. [2]
Ans: SELECT *
FROM employee
WHERE Department = 'Account';
e) Write the SQL statement to modify the "Name" column into "First Name". [2]
Ans: ALTER TABLE employee
CHANGE Name FirstName VARCHAR(20);
f) Find out the employee whose department account and salary greater than 25,000. [3]
Ans: SELECT *
FROM employee
WHERE Department = 'Account'
AND BasicSalary > 25000;
g) Find out the maximum allowance of the employee. [2]
Ans: SELECT MAX(Allowance) AS Maximum_Allowance FROM employee;
Make StudentID primary key in Student. Make ResultID primary key in Result and StudentID foreign key.
Ans:
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
Address VARCHAR(100),
PhoneNumber VARCHAR(20)
);
CREATE TABLE Result ( ResultID INT PRIMARY KEY, Subject VARCHAR(50), Marks INT, StudentID INT, FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ); -- Inserting data into Student Table INSERT INTO Student (StudentID, StudentName, Address, PhoneNumber) VALUES (101, "Sita Sharma", "Pokhara", "061-11111"), (102, "Ram Bahadur", "Lalitpur", "01-22222"), (103, "Mina Rai", "Kathmandu", "01-33333"); -- Inserting data into Result Table INSERT INTO Result (ResultID, Subject, Marks, StudentID) VALUES (201, "English", 78, 101), (202, "Math", 82, 102), (203, "Science", 65, 103); 3. Display all records from both tables. Ans: SELECT * FROM Student; SELECT * FROM Result; 4. Display students who live in "Kathmandu". Ans: SELECT * FROM Student WHERE Address = "Kathmandu"; 5. Display students whose name starts with "M". Ans: SELECT * FROM Student WHERE StudentName LIKE "M%"; 6. Display students whose name ends with "a". Ans: SELECT * FROM Student WHERE StudentName LIKE "%a"; 7. Display subjects with marks greater than 70. Ans: SELECT Subject FROM Result WHERE Marks > 70; 8. Update "Mina Rai" marks in Science to 75. Ans: UPDATE Result SET Marks = 75 WHERE StudentID = 103 AND Subject = "Science"; 9. Change "Sita Sharma" phone number to "061-22222". Ans: UPDATE Student SET PhoneNumber = '061-22222' WHERE StudentName = "Sita Sharma"; 10. Delete record of "Ram Bahadur". Ans: DELETE FROM Result WHERE StudentID = 102;
DELETE FROM Student WHERE StudentName = "Ram Bahadur";
SQL Lab -1
Q1) Create a database "Library".
Ans: CREATE DATABASE Library;Q2) Go inside the database "Library". Ans: USE Library
Q3) Create a table named "book".
| bookid | bookname | price |
|---|---|---|
| 101 | Muna Madan | 25.25 |
| 102 | Ramayan | 1300.95 |
| 103 | Gauri | 45.25 |
| 104 | Half Girlfriend | 670.23 |
| 105 | 2 states | 245.75 |
Q4) Display all records of book. Ans: SELECT * FROM book;
Q5) Display bookname and price only from table book. Ans: SELECT bookname,price FROM book;
Q6) Add a column bookauthor after bookname. Ans: alter table book add bookauthor varchar(100) after bookname;
Q7) Update the book author name of id 105 in the table book Ans: Update book SET bookauthor="Chetan Bhagat" Where bookid=105;
Q8) Update multiple author name at one time in the table book UPDATE book SET bookauthor = CASE bookid WHEN 101 THEN 'L. P. Devkota' WHEN 102 THEN 'Valmiki' WHEN 103 THEN 'Madhav Ghimire' WHEN 104 THEN 'Chetan Bhagat' END WHERE bookid IN (101, 102, 103,104);
Q9) Display the record of bookname which start with letter "M". Ans:select * from book where bookname like "M%";
Q10) Display the record of bookname which start with letter "H". Ans:select * from book where bookname like "H%";
Q11) Display the record of bookname which end with letter "n". Ans:select * from book where bookname like "%n";
Q12) Display the record of bookname with second letter "a". Ans:select * from book where bookname like "_a%";
Q13) Display the bookname and price which start with letter "M". Ans:select bookname,price from book where bookname like "M%";
Q14) Update the bookauthor name from L.P. Devkota to Laxmi Prasad Devkota. Ans:update book set bookauthor = "Laxmi Prasad Devkota" Where bookid=101;
Q15) Display the maximum price of book. Ans:select max(price) from book;
Q16) Count and display the total records of table book. Ans:select count(*) from book;
Q17) Display the lowest price book details. Ans:select bookid,bookname,bookauthor,min(price) from book ;
Q18) Display the average price of book . Ans:select avg(price) from book;
Q19) Display the sum of all bookprice. Ans:select sum(price) from book;
Q20) Delete the record of bookid 105. Ans:delete from book where bookid=105;
Q21) Delete all records of table book. Ans:delete from book;
SQL Lab -2
Consider the following "CUSTOMER" table:| Cust_Id | Cust_Name | Address | Contact_No |
|---|---|---|---|
| C005 | Rakesh | Birgunj | 9852011111 |
| C010 | Mahesh | Nepalgunj | 9862022222 |
| C012 | Aniket | Butwal | 9805033333 |
| C020 | Hitesh | Pokhara | 9812344444 |
| C025 | Mandip | Birgunj | 9842055555 |
| C028 | Hari | Pokhara | 9842166666 |
SQL Lab -3
Consider the following "employee" table used to store the information about employee.| Column Name | Data Type | Size | Attribute |
|---|---|---|---|
| EmpNo | varchar | 6 | Primary Key |
| Name | varchar | 20 | Not null |
| Address | varchar | 20 | Not null |
| Department | varchar | 10 | Not null |
| Allowance | numeric | 18,2 | Not null |
| BasicSalary | numeric | 18,2 | Not null |
| Emp No | Name | Address | Department | Allowance | Basic Salary |
|---|---|---|---|---|---|
| E001 | Deepak Tiwari | Pokhara | Account | 2700 | 30000 |
| E002 | Ram Gupta | Birgunj | HR | 3200 | 25000 |
| E003 | Rohan Kumar | Bharatpur | IT | 2400 | 40000 |
| E004 | Binod Sharma | Kathmandu | Manager | 3700 | 60000 |
| E005 | Maya G.C. | Butwal | Account | 5200 | 32000 |
| E006 | Santa Amgain | Dharan | Marketing | 2000 | 27000 |
| E007 | Sabita Tiwari | Gorakha | Sales | 3000 | 18000 |
| E008 | Sulav Adhikari | Siraha | HRM | 2000 | 32000 |
SQL Lab -4
1. Make a database "College".
Ans:CREATE DATABASE College;
USE College;
2. Under this database, make the following two tables and insert the given records.| StudentID | StudentName | Address | PhoneNumber |
|---|---|---|---|
| 101 | Sita Sharma | Pokhara | 061-11111 |
| 102 | Ram Bahadur | Lalitpur | 01-22222 |
| 103 | Mina Rai | Kathmandu | 01-33333 |
| ResultID | Subject | Marks | StudentID |
|---|---|---|---|
| 201 | English | 78 | 101 |
| 202 | Math | 82 | 102 |
| 203 | Science | 65 | 103 |
CREATE TABLE Result ( ResultID INT PRIMARY KEY, Subject VARCHAR(50), Marks INT, StudentID INT, FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ); -- Inserting data into Student Table INSERT INTO Student (StudentID, StudentName, Address, PhoneNumber) VALUES (101, "Sita Sharma", "Pokhara", "061-11111"), (102, "Ram Bahadur", "Lalitpur", "01-22222"), (103, "Mina Rai", "Kathmandu", "01-33333"); -- Inserting data into Result Table INSERT INTO Result (ResultID, Subject, Marks, StudentID) VALUES (201, "English", 78, 101), (202, "Math", 82, 102), (203, "Science", 65, 103); 3. Display all records from both tables. Ans: SELECT * FROM Student; SELECT * FROM Result; 4. Display students who live in "Kathmandu". Ans: SELECT * FROM Student WHERE Address = "Kathmandu"; 5. Display students whose name starts with "M". Ans: SELECT * FROM Student WHERE StudentName LIKE "M%"; 6. Display students whose name ends with "a". Ans: SELECT * FROM Student WHERE StudentName LIKE "%a"; 7. Display subjects with marks greater than 70. Ans: SELECT Subject FROM Result WHERE Marks > 70; 8. Update "Mina Rai" marks in Science to 75. Ans: UPDATE Result SET Marks = 75 WHERE StudentID = 103 AND Subject = "Science"; 9. Change "Sita Sharma" phone number to "061-22222". Ans: UPDATE Student SET PhoneNumber = '061-22222' WHERE StudentName = "Sita Sharma"; 10. Delete record of "Ram Bahadur". Ans: DELETE FROM Result WHERE StudentID = 102;
DELETE FROM Student WHERE StudentName = "Ram Bahadur";
