Scribd
Upload
14 views21 pages

Prajwal Kumar Ray ISM Lab File

The document outlines a lab report for an Information Systems Management course at the Army Institute of Management and Technology, detailing the creation and querying of various database tables related to employees, projects, salesmen, and customers. It includes SQL commands for selecting, updating, and aggregating data from these tables. The report is structured with a table of contents and specific queries with corresponding SQL commands for data retrieval and manipulation.

Uploaded by

prajwalray2608
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
14 views21 pages

Prajwal Kumar Ray ISM Lab File

The document outlines a lab report for an Information Systems Management course at the Army Institute of Management and Technology, detailing the creation and querying of various database tables related to employees, projects, salesmen, and customers. It includes SQL commands for selecting, updating, and aggregating data from these tables. The report is structured with a table of contents and specific queries with corresponding SQL commands for data retrieval and manipulation.

Uploaded by

prajwalray2608
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 21

ARMY INSTITUE OF MANAGEMENT AND TECHNOLOGY

GREATER NOIDA

INFORMATION SYSTEM MANAGEMENT LAB


(BBA 307)

SUBMITTED BY: SUBMITTED TO:


Prajwal Kumar Ray DR. PAWAN KUMAR
BBA 2021-24
Roll no.- 0391
Table of Content
S No. Content Page No

1. Table and database formulation 3-6

2. Questions or Queries 7-11

3. Table and database formulation 12-13

4. Questions or queries 14-22


* Create the following table with database AIMT.

Table name: - Employess

Table: - Emp_Dept

Table: - Emp_Salary
Table: - Project

Table: - Country

Table: - Client_Table
Table: - Emp_Project

Questions
1) Select the details of the employee whose name starts with p.
Ans.

COMMAND- SELECT *FROM Employee3


WHERE empname LIKE 'P%';

2) How many permanent candidate take salary more than 5000.


Ans.

COMMAND- SELECT count(*) FROM EmpSalary


WHERE ispermanent = 'yes' AND salary > 5000;

3) Select the detail of employee whose email Id is in gmail.


Ans.

COMMAND- SELECT *FROM Employee3


WHERE emailid LIKE '%@gmail.com';

4) Select the details of the employee who work either for department E-104 or E-
102.
Ans)

COMMAND- SELECT *FROM Employee3


WHERE dept = 'E-104' OR dept = 'E-102';
5) What is the department name for DeptID E-102?
Ans)

COMMAND- SELECT dept_name FROM emp_dept


WHERE dept_id = 'E-102';

6) What is total salary that is paid to permanent employees?


Ans)
COMMAND- SELECT SUM(salary) AS total_salary_paid FROM EmpSalary
WHERE ispermanent = 'yes';

7) List name of all emplyees whose name ends with h.


Ans)

COMMAND- SELECT empname FROM Employee


WHERE empname LIKE '%H';
8) How many project started in year 2010?
Ans)

COMMAND- SELECT COUNT(*)FROM EmpProject


WHERE startYear = 2010;
9) How many project started and finished in the same year.
Ans)
COMMAND- SELECT COUNT(*)FROM EmpProject
WHERE startYear = endYear AND startYear IS NOT NULL AND
endYear IS NOT NULL

10) Select the name of the employee whose name’s 3 rd character is


‘a’.
Ans)

COMMAND- SELECT empname FROM Employee3


WHERE SUBSTRING(empname, 3, 1) = 'A';

11) Update the Parvesh email id to [email protected].


Ans)

COMMAND- UPDATE Employee3


SET emailid = '[email protected]'
WHERE empname = 'parvesh';
Select*from Employee3;
12) Change the column name EndYear to EYear in EmpProject.
Ans)

COMMAND- ALTER TABLE EmpProject


RENAME COLUMN endYear TO EYear;
select*from EmpProject;
13) Apply join concepts on above tables.
Ans)

COMMAND- SELECT *FROM Project P


INNER JOIN EmpSalary ES ON P.project_id = ES.empid;

Table :- SALESMAN
Table :- ORDERS
Table :- CUSTOMER

Questions
1) Display name, commission, and city of all the salesmen.
Ans)

COMMAND- Select name,city,comission from salesman;

2) Retrieve salesman id of all salesmen from orders table without any repeats
Ans)

COMMAND- SELECT DISTINCT SALESMAN_ID FROM ORDERS;

3) Retrieve salesman id of all salesmen from orders table with customer id 3002.
Ans)

COMMAND- SELECT DISTINCT SALESMAN_ID FROM ORDERS


WHERE CUSTOMER_ID = 3002;
4) Display names and city of salesman, who belongs to the city of Rome.
Ans)

COMMAND- SELECT NAME, CITY FROM SALESMAN


WHERE CITY = 'ROME';
5) Display all the information for those customers with a grade of 200.
Ans)

COMMAND- SELECT * FROM CUSTOMER_2


WHERE GRADE = 200;
6) Display the order number, order date and the purchase amount for order(s) which
will be delivered by the salesman with ID 5001.
Ans)

COMMAND- SELECT ORD_NO, ORD_DATE, PURCH_AMT FROM ORDERS


WHERE SALESMAN_ID = 5001;
7) Display all the customers, who are either belongs to the city New York or not had a
grade above 100.
Ans)

COMMAND- SELECT * FROM customer_2


WHERE city = 'New York' OR NOT grade > 100;
8) Find those salesmen with all information who gets the commission within a range of
0.12 and 0.14.
Ans)

COMMAND- SELECT salesman_id, name, city, comission FROM salesman


WHERE (comission > 0.10 AND comission < 0.12);
9) Find all those customers with all information whose names are ending with the letter
'n'.
Ans)

COMMAND- SELECT *FROM customer_2


WHERE cust_name LIKE '%n';
10) Find that customer with all information who does not get any grade except NULL.
Ans)

COMMAND- SELECT * FROM customer_2


WHERE grade IS NULL
11) Find the total purchase amount of all orders.
Ans)

COMMAND- SELECT sum(PURCH_AMT)


FROM ORDERS;
12) Find the number of salesman currently listing for all of their customers.
Ans)

COMMAND- SELECT COUNT(SALESMAN_ID)


FROM ORDERS;
13) Find the highest grade for each of the cities of the customers.
Ans)

COMMAND- SELECT city, MAX(grade) FROM customer_2


GROUP BY city;

14) Find the highest purchase amount ordered by the each customer with their ID and
highest purchase amount.
Ans)

COMMAND- SELECT customer_id, MAX(purch_amt) FROM orders


GROUP BY CUSTOMER_ID;
15) Find the highest purchase amount ordered by the each customer on a particular
date with their ID, order date and highest purchase amount.
Ans)

COMMAND- SELECT customer_id, ord_date, MAX(purch_amt) FROM orders


GROUP BY customer_id, ord_date;

16) Find the highest purchase amount on a date '2012-08-17' for each salesman with
their ID.
Ans)

COMMAND- SELECT customer_id, ord_date, MAX(purch_amt) FROM orders


GROUP BY customer_id, ord_date;
17) Find the highest purchase amount with their customer ID and order date, for only
those customers who have the highest purchase amount in a day is more than 2000.
Ans)

COMMAND- SELECT customer_id, ord_date, MAX(purch_amt) FROM orders


GROUP BY CUSTOMER_ID, ORD_DATE
HAVING MAX(PURCH_AMT)>2000.00;
18) Write a SQL statement that counts all orders for the date August 17th, 2012.
Ans)

COMMAND- SELECT COUNT(*) FROM ORDERS


WHERE ORD_DATE='2012-0817';
19) Show all the details of the customers whose city name starts with N.
Ans)

COMMAND- SELECT *FROM customer_2


WHERE city LIKE 'N%';
20) Calculate the following function.
i- Find the Sum of commission given to all the salesman.
Ans)

COMMAND- SELECT SUM(comission) AS total_comission


FROM salesman;
ii) Find the maximum and minimum commission given to any salesman.
Ans)

COMMAND- SELECT MAX(comission) AS max_comission, MIN(comission) AS


min_comission FROM salesman
iii) Count the total number of customers.
Ans)

COMMAND- SELECT COUNT(*) AS total_customers


FROM customer_2;
iv) What is the average commission given to the salesman?
Ans)

COMMAND- SELECT AVG(comission) AS average_comission


FROM salesman;

You might also like