SQL Lab Exercise 1
- Write a SQL statement to create a simple table countries including coulmns country_id, country_name and continent.
- Write a SQL statement to create a simple table student including columns roll,full_name,DateOfBirth and age.
- Write a SQL statement to insert some values in a table countries.
- Write a SQL statement to insert some values in a table student.
- 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.
- 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'.
- 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'--/--/----'.
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));
CREATE TABLE student
(
roll int PRIMARY KEY,
full_name varchar(30),
DateofBirth date,
age int
);
INSERT INTO countries
(country_id, country_name, continent) VALUES
(101,'Japan','Asia'),
(102,'Nepal','Asia');
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);
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
1st Part:
CREATE TABLE countries (
country_id (int),
country_name varchar(30),
continent varchar(30));
2nd part:
SELECT * FROM countries WHERE continent='Asia'
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:
- Create database mydatabase
- CREATE TABLE student_detail(student roll number (int) (primary key), student name(varchar), student address (varchar),student dob(date and time)).
- CREATE TABLE student_marksheet (student roll number (int) (primary key), student total marks(int), student division(varchar)).
- 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 a table empdept with emp number(int) (primary key), dept name(char), empworkingyear(int).
- Modify the table student_marksheet using alter command and add another field grade(varchar).
- Remove the table empdept using drop command.
CREATE DATABASE MYDATABASE
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
);
CREATE TABLE student_marksheet (
roll_number int primary key,
s_total_marks int,
s_division varchar(30));
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 );
create table empdept(
emp_number int primary key,
dept_name varchar(20),
empworkingyear int);
ALTER TABLE student_marksheet ADD grade varchar(5);
DROP TABLE empdept;
SQL Lab Exercise 3
A. Write SQL queries using Data Manipulation Language(DML) statements:
- 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.
- Display the table student using select command: You should display all records from above table: student_detail, student_marksheet, employee.
- Update student address in student_detail using update command.
- Update student total marks in student_marksheet table using update command.
- Update student date of birth in employee table using update command.
- Delete a row from student_detail, student_marksheet and employee tables.
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');
SELECT * FROM student_detail
UPDATE student_detail
SET `s_address` = 'Bagmati'
WHERE `roll_number` = 1;
UPDATE `student_marksheet` SET s_total_marks=250 WHERE `roll_number`=5;
UPDATE employee SET
emp_dob = '1988-05-23 16:30:00'
WHERE emp_no = 1
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.
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;