Scribd
Upload
18 views22 pages

MySQL Employee and Student Queries Guide

The document contains questions about SQL queries on various tables like Employee, Student, SchoolBus, etc. It asks to write queries to select, insert, update and display data from these tables based on various conditions on columns like name, department, salary, marks, etc. and order the results. It also asks questions about modifying the structure of tables by adding or changing columns.

Uploaded by

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

MySQL Employee and Student Queries Guide

The document contains questions about SQL queries on various tables like Employee, Student, SchoolBus, etc. It asks to write queries to select, insert, update and display data from these tables based on various conditions on columns like name, department, salary, marks, etc. and order the results. It also asks questions about modifying the structure of tables by adding or changing columns.

Uploaded by

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

MySQL Practical File

Q1) Create a table Employee2 as per the given description and


insert 7 records in it: -

Final Table: -

1|P ag e
Q2) Use the above table ‘Employee’ to answer the questions: -
a) To display all the details on screen.

b) To display name and salary of the employee.

c) To display name and date of joining of those employees who


work in Sales department.

2|P ag e
d) To display all the details of those employees whose name begin
with ‘A’ and ends with ‘N’.

e) To display all the details of those employees who work in


Purchase department and earns more than 50K per month.

f) To display name and annual salary of those employees who


work in accounts department.

g) To display the department without repeating duplicates.

h) To insert the given data in the table: (8, ’Pooja’, NULL, ’2021-09-
09’, 90000)

3|P ag e
i) To display name and salary of the employee in the given format:
Seema earns 80000
Pooja earns 90000

j) To display name of those employees who have not assigned any


department.

k) To display name and salary of all the employees in desc order by


date of joining.

4|P ag e
l) To increase the salary of all the employees by 10%.

m) To assign Sales department to ‘Pooja’.

n) To add one more column email_id in the table.

o) To store email ids in the column email_id.

p) To increase the size of name column by 5.

5|P ag e
q) To change the column name Sal_PM with salary.

Q3) Table Student: -

a) To display details of those students who have not provided


email_id.

6|P ag e
b) To display names of ‘Aravali’ house students in upper case.

c) To display names of those students who are born in ‘August’


month.

d) To display the name and email_id of those students who have a


account.

7|P ag e
e) To display no. of students of class 12D.

f) To display names of all the students in asc order.

g) To display average TM of 12C students.

8|P ag e
h) To display name and house of ‘Dhauladhar’ house students.

i) To display year of dob along with student name.

9|P ag e
j) To display first three letters of students name.

k) To display the position of ‘ten’ in all the name.

10 | P a g e
l) To display a sub-string ‘Ven’ from Manvendra.

m)To display total marks of all the ‘Shivalik’ house students after
rounding off the fraction part.

n) To display frequency of students in each house along with the


house name.

o) To display house name and total marks scored by each house’s


students. Display only whose details where total marks is more

11 | P a g e
than 1000.

p) To display names of those students who have minimum seven


letters in the name

Q4) Table SchoolBus, Inserting the data: -

Final Table: -

12 | P a g e
a) To show all information of students where capacity is more than
no. of student in order of rtno.

b) To show area_covered for buses more than 20KM, but charges


less than 80000.

c) To show the area covered by Bhalla Co. and kisan tours.

13 | P a g e
d) To show the names of all the transporters without repeating
their names.

e) To show all the bus routed number where the distance is less
than 30.

f) To show the area whose names end with ‘r’ in decreasing order
by area covered.

14 | P a g e
g) To show rtno, area_covered and average cost per student for all
routes where average cost per student is charges/noofstudent.

h) To show the area covered with bus routes number where


distance is more than 30.

15 | P a g e
i) To show the number of areas covered by each transporter.

j) To Show the maximum capacity of a bus

16 | P a g e
k) To show transporter wise total number of students traveling.

l) Add new record with data: (11,’Moti bagh’,35,32,10,’kisan


tours’,35000)

Q5) Table Employee & Department

a) Display the Salary, Zone, and Grade of all the employees.

17 | P a g e
b) Display the name of all the employees along with their annual
salaries. The Salary column should be given the name “Annual
Salary”.

c) Display the names of various zones from the table Employee. A


zone man should appear only once.

d) Display the details of all the employees who are getting a salary
of more than 35000 in department 30.

18 | P a g e
e) Display the details of all the employees whose salary is between
32000 and 38000.

f) Display the names of all the employees who are working in


department 20 or 30.

g) Display the name, salary, and age of all the employees whose
names start with ‘M’.

19 | P a g e
h) Display the details of all the employees whose names contain ‘a’
as the second character.

i) Display the highest and lowest salaries being paid in department


10.

j) Display the number of employees working in department 10.

20 | P a g e
k) Display the name and salary of all the employees in the
ascending order of those salaries.

l) Display the total number of employees in each department.

21 | P a g e
m) Put the grade B for all whose grade is NULL.

n) Display the Name and Department Name of all the employees.

o) Display the names of all the employees whose salary is out of


the specifies range for the corresponding department.

22 | P a g e

Common questions

Powered by AI

Changing the column name from 'Sal_PM' to 'salary' makes the database more intuitive and user-friendly by using a more universally understood term, thereby improving accessibility and reducing the learning curve for new users .

The SQL query for displaying unique department entries is SELECT DISTINCT Department FROM Employee2. This is important for data integrity to ensure that each department is represented once, which simplifies reporting and prevents data redundancy .

Displaying student names in uppercase enhances visibility and emphasis, particularly when distinguishing certain groups from others. However, consistent formatting should be maintained across databases to avoid confusion in data presentation and interpretation .

To modify the Employee table to store email IDs, you need to add a new column named 'email_id' using an ALTER TABLE command. This modification is necessary to store email addresses of employees, which is crucial for communication and record-keeping .

The query is: SELECT Name FROM Employee2 WHERE Department IS NULL. This insight is crucial for HR planning as it identifies gaps in departmental assignments that could lead to inefficiencies or resource underutilization, enabling HR to adjust staffing strategically .

The SQL statement to increase salaries by 10% is: UPDATE Employee2 SET Salary = Salary * 1.10. To ensure data accuracy, a transaction should be implemented that allows for rollback in case of an error, particularly when dealing with large datasets, to prevent partial updates .

To display the number of students in each house, use the SQL query: SELECT House, COUNT(*) AS NumberOfStudents FROM Student GROUP BY House. This information is pivotal for educational data analysis as it helps in resource allocation, monitoring student distribution, and identifying trends in house participation .

The SQL query is: SELECT AVG(TotalMarks) FROM Student WHERE Class = '12C'. Calculating average total marks provides an overall performance measure, helping educators identify trends in academic achievement and adjust instructional methods accordingly .

The SQL query is: SELECT RouteNumber FROM SchoolBus WHERE Distance > 30. This data aids transport management by identifying routes that may require resource allocation adjustments, such as additional vehicles or increased maintenance, to ensure efficient and cost-effective operations .

Use the SQL queries: SELECT MAX(Salary) AS HighestSalary FROM Employee WHERE Department = 10; and SELECT MIN(Salary) AS LowestSalary FROM Employee WHERE Department = 10. This information guides management in making informed decisions regarding salary adjustments and maintaining competitive compensation .

You might also like