SQL Lab Sheet

SQL Lab Exercise 1

1. Write a SQL statement to create a simple table countries including coulmns country_id, country_name and continent.
mysql -u root -p press Enter key Enter password: Press Enter key Create database Lab1; USE Lab1; CREATE TABLE countries ( country_id int, country_name varchar(30), continent varchar(30));
2. Write a SQL statement to create a simple table student including columns roll,full_name,DateOfBirth and age.
CREATE TABLE student ( roll int PRIMARY KEY, full_name varchar(30), DateofBirth date, age int );
3. Write a SQL statement to insert some values in a table countries.
INSERT INTO countries (country_id, country_name, continent) VALUES (101,'Japan','Asia'), (102,'Nepal','Asia');
4. Write a SQL statement to insert some values in a table student.
INSERT INTO student(roll, full_name, DateofBirth, age) VALUES (1,'Purushottam Lal Karn','1988/05/23',33), (2,'Alex Lal Karn','2017/10/16',4);
5. Write a SQL statement to create a table named jobs including columns job_id, job_title, min_salary, max_salary and check whether the max_salary amount exceeding the upper limit 25000.
1st Part: CREATE TABLE jobs ( job_id int, job_title varchar(30), min_salary int, max_salary int ); After inserting some values 2nd Part: SELECT * FROM jobs WHERE max_salary>25000
6. Write a SQL statement to create a table named countries including columns country_id,country_name and continent and display those countries which are lie in continent 'Asia'.
1st Part: CREATE TABLE countries ( country_id (int), country_name varchar(30), continent varchar(30)); 2nd part: SELECT * FROM countries WHERE continent='Asia'
7. Write a SQL statement to create a table named job_history including columns employee_id, start_date, end_date,job_id and department_id and make sure that the value against column end_date will be entered at the time of insertion to the format like'--/--/----'.
CREATE TABLE job_history ( emp_id int, start_date varchar(20), end_date varchar (20) , job_id int, dept_id int); INSERT INTO job_history (emp_id, start_date, end_date, job_id, dept_id) VALUES (102,'01/02/2020','14/10/2021',120,02);

SQL Lab Exercise 2

A. Write the SQL queries using Data Definition Language (DDL) statements:

1. Create database mydatabase
CREATE DATABASE MYDATABASE
2. CREATE TABLE student_detail(student roll number (int) (primary key), student name(varchar), student address (varchar),student dob(date and time)).
use mydatabase in console and press ctrl + enter together CREATE TABLE student_detail ( roll_number int primary key, s_name varchar(30), s_address varchar(30), s_dob datetime );
3. CREATE TABLE student_marksheet (student roll number (int) (primary key), student total marks(int), student division(varchar)).
CREATE TABLE student_marksheet ( roll_number int primary key, s_total_marks int, s_division varchar(30));
4. Create a table employee with emp number(int) (primary key), emp name(varchar), emp dob(date and time), emp address(varchar), emp dept(varchar), empsal (int).
Create table employee ( emp_no int primary key, emp_name varchar(30), emp_dob datetime, emp_address varchar(30), emp_dept varchar(30), empsal int );
5. create a table empdept with emp number(int) (primary key), dept name(char), empworkingyear(int).
create table empdept( emp_number int primary key, dept_name varchar(20), empworkingyear int);
6. Modify the table student_marksheet using alter command and add another field grade(varchar).
ALTER TABLE student_marksheet ADD grade varchar(5);
7. Remove the table empdept using drop command.
DROP TABLE empdept;

SQL Lab Exercise 3

A. Write SQL queries using Data Manipulation Language(DML) statements:

1. Insert values into student table with field names using insert command. You have to add 4 to 5 records in a table student_detail, student_marksheet, employee.
INSERT INTO `student_detail`(`roll_number`, `s_name`, `s_address`, `s_dob`) VALUES (1,'ast','jorpati','2000-02-02'),(2,'lkm','jorpati-01','2000-02-03'); INSERT INTO `student_marksheet`(`roll_number`, `s_total_marks`, `s_division`, `grade`) VALUES (1,500,'1st','A'),(2,600,'1st','A'),(3,700,'1st','A'),(4,300,'2st','A'),(5,125,'1st','A');
2. Display the table student using select command: You should display all records from above table: student_detail, student_marksheet, employee.
SELECT * FROM student_detail
3. Update student address in student_detail using update command.
UPDATE student_detail SET `s_address` = 'Bagmati' WHERE `roll_number` = 1;
4. Update student total marks in student_marksheet table using update command.
UPDATE `student_marksheet` SET s_total_marks=250 WHERE `roll_number`=5;
5. Update student date of birth in employee table using update command.
UPDATE employee SET emp_dob = '1988-05-23 16:30:00' WHERE emp_no = 1
6. Delete a row from student_detail, student_marksheet and employee tables.
DELETE FROM `student_marksheet` WHERE `roll_number`=1

Extra Practice Question

Process to connect to database

(1) Open XAMPP (2) START Apache and MYSQL (3) Click on SHELL

Check whether the required database is present or not.

Q.1 Make a database named 'SECTIONC2' and use it. Q.2 Write DDL commands/SQL program to create a table named BOOK with following fields.

BOOK

columns/fields	Data type	key
book_id	int	primary
book_name	varchar(30)
book_author	varchar(100)

CREATE TABLE BOOK( BOOK_ID INT PRIMARY KEY, BOOK_NAME VARCHAR(30), BOOK_AUTHOR VARCHAR(100) );

Q.3 Insert the following four records using SQL commands.

book_id	book_name	book_author
101	Muna Madan	Laxmi Prasad Devkota
102	Summer Love	Laxmi Prasad Devkota
103	Ramayan	Bhanu Bhakta Acharya
104	Gauri	Madhav Prasad Ghimire

INSERT INTO BOOK (BOOK_ID,BOOK_NAME,BOOK_AUTHOR) VALUES (101,'Muna Madan','Laxmi Prasad Devkota'); INSERT INTO BOOK (BOOK_ID,BOOK_NAME,BOOK_AUTHOR) VALUES (102,'Summer Love','Laxmi Prasad Devkota'); INSERT INTO BOOK (BOOK_ID,BOOK_NAME,BOOK_AUTHOR) VALUES (103,'Ramayan','Bhanu Bhakta Acharya'); INSERT INTO BOOK (BOOK_ID,BOOK_NAME,BOOK_AUTHOR) VALUES (104,'Gauri','Madhav Prasad Ghimire')

Q.4 View the all records of table BOOK.

SELECT * FROM BOOK;

Q.5 Update the writer name of Summer Love with Subin Bhattarai and book name Ramayan with Bhanu Bhakta Ramayan.

UPDATE BOOK SET BOOK_AUTHOR='Subin Bhattarai' WHERE BOOK_ID=102; UPDATE BOOK SET BOOK_NAME='Bhanu Bhakta Ramayan' WHERE BOOK_ID=103;

Q.6 Add one more field/column Price with data type float.

ALTER TABLE BOOK ADD PRICE FLOAT;

Q.7 Update the price of MUNA MADAN to 150.75

UPDATE BOOK SET PRICE=150.75 WHERE BOOK_ID=101;

Q.8 Similarly, Update the price of SUMMER LOVE(350), BHANU BHAKTA RAMAYAN(550.50) and GAURI (120.65).

UPDATE BOOK SET PRICE=350.00 WHERE BOOK_ID=102; UPDATE BOOK SET PRICE=550.50 WHERE BOOK_ID=103; UPDATE BOOK SET PRICE=120.65 WHERE BOOK_ID=104;

Q.9 Delete the record of Book (GAURI).

DELETE FROM BOOK WHERE BOOK_ID=104;