Department of Information Technology
Assignment No: 2
Subject: SQL LAB
Semester: III Class: SEIT
Date of Display: Date Of Submission:
Batch Questions Bloom's Lab
Taxonomy Outcomes
Level
Q1)Write a SQL query to create table salesman Analyzing
which has the following columns salesman_id | ITL303.4
name | city where id starts from 5001 till 5007.
Q2)Write a SQL statement that displays all the Analyzing
ITL303.4
information about all salespeople.
Q3)CREATE an order table with columns ord_no Analyzing
purch_amt ord_date customer_id salesman_id ITL303.4
where sales id is 5001 till 5007
Common
Q4) From the order table, write a SQL query to Analyzing
calculate total purchase amount of all orders. ITL303.4
Return total purchase amount.
Q5) From the following table, write a SQL query Analyzing
to calculate the average purchase amount of all ITL303.4
orders.
Q6) Write a query to display sales id order id city Analyzing
together. ITL303.4
Q7) Explain Authorization Using Grant and Understanding
Revoke with example. ITL303.4
Q8) Explain Transaction and Concurrency control Understanding
techniques using locks. ITL302.6
Department of Information Technology
Assignment No: 2 Answers
Subject: SQL LAB
Semester: III Class: SEIT
Date of Display: Date Of Submission:
Assignment (2) Answers
Write a SQL query to create table salesman which has the following columns salesman_id |
name | city where id starts from 5001 till 5007.
SYNTAX:
CREATE TABLE salesman (
salesman_id INT PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(100)
);
INSERT INTO salesman (salesman_id, name, city)
INSERT INTO salesman VALUES(5001, 'Rohit ', 'Mumbai'),
INSERT INTO salesman VALUES(5002, 'Rishab ', 'Delhi'),
INSERT INTO salesman VALUES (5003, 'Virat', 'Bengaluru'),
INSERT INTO salesman VALUES (5004, 'Shikhar', 'Chennai'),
INSERT INTO salesman VALUES (5005, 'Rahul', 'Kolkata'),
INSERT INTO salesman VALUES(5006, 'Niraj', 'Hyderabad'),
INSERT INTO salesman VALUES(5007, 'Vijay', 'Pune');
OUTPUT:
;
Q2)
Write a SQL statement that displays all the information about all salespeople.
SYNTAX:
-- View the contents of the salesman table
SELECT * FROM salesman;
OUTPUT:
Q3)
CREATE an order table with columns ord_no ,purch_amt ord_date customer_id salesman_id
where sales id is 5001 till 5007
SYNTAX:
-- Create the orders table
CREATE TABLE orderss (
ord_no INT PRIMARY KEY,
purch_amt DECIMAL(10, 2),
ord_date DATE,
customer_id INT,
salesman_id INT,
-- Insert sample records into the orders table
INSERT INTO orderss (ord_no, purch_amt, ord_date, customer_id, salesman_id)
VALUES (1, 3000.00, '10-02-2024', 101, 5001),
INSERT INTO orderss (ord_no, purch_amt, ord_date, customer_id, salesman_id)
VALUES (2, 3500.00, '15-02-2024', 102, 5002),
INSERT INTO orderss (ord_no, purch_amt, ord_date, customer_id, salesman_id)
VALUES (3, 4200.00, '23-03-2024', 103, 5003),
INSERT INTO orderss (ord_no, purch_amt, ord_date, customer_id, salesman_id)
VALUES (4, 4000.00, '23-04-2024', 104, 5004),
INSERT INTO orderss (ord_no, purch_amt, ord_date, customer_id, salesman_id)
;
VALUES(5, 2200.50, '20-05-2024', 105, 5005),
INSERT INTO orderss (ord_no, purch_amt, ord_date, customer_id, salesman_id)
VALUES(6, 2000.00, '02-06-2024', 106, 5006)
INSERT INTO orderss (ord_no, purch_amt, ord_date, customer_id, salesman_id)
VALUES(7, 1500.00, '21-06-2024', 106, 5007),
SELECT * FROM orderss;
OUTPUT:
Q4)
From the order table, write a SQL query to calculate total purchase amount of all orders. Return
total purchase amount.
SYNTAX:
SELECT SUM(purch_amt) AS total_purchase_amount
FROM orderss;
OUTPUT:
;
Q5)
From the following table, write a SQL query to calculate the average purchase amount of all
orders.
SYNTAX:
SELECT AVG(purch_amt) AS average_purchase_amount
FROM orders;
OUTPUT:
Q6)
Write a query to display sales id order id city together.
SYNTAX:
SELECT
o.salesman_id,
o.ord_no,
[Link]
FROM
orderss
salesman s ON o.salesman_id = s.salesman_id;
OUTPUT:
;
Q7) Explain Authorization Using Grant and Revoke with example
Q8) Explain Transaction and Concurrency control techniques using locks.