Create an Employee table for the following fields
(Empno, Empname, Empaddress, salary, dept)
Calculate the total of the salary field
List the count of employees who have salary less than 50000
Find the details of employee having maximum salary
Add a new field title to the emplyee table with a default value as junior software engineer
Update the title of employee whose employee number 2004 to "Senor software engineer"
Delete the employee record of employee whose emplyee number is 2005
TEST CASE
mysql> select * from employee;
+-------+---------+------------+--------+--------------+
| empNo | empName | empAddress | salary | dept |
+-------+---------+------------+--------+--------------+
| 2001 | Hari | Palakkad | 30000 | Reasearch |
| 2002 | Devi | Kasaragod | 40000 | Network |
| 2003 | Gupta | Kannur | 50000 | System Admin |
| 2004 | Ravi | Calicut | 60000 | HR |
| 2005 | Rani | Calicut | 70000 | HR |
+-------+---------+------------+--------+--------------+
5 rows in set (0.00 sec)
Experiment -2
Create Student table for the following fields:
(studentNo, studentName, maths, physics, chemistry, cProgramming, department, address)
- List the studentno and studentname who is having marks less than 50 in C-programming in ascending order of their marks.
- List the studentno, studentname and department, in descending order of their department and ascending order of their name.
List the name of students whose name starts with ‘S’.
Find the name of student who obtained second largest mark in in C-programming.
List the number of students in each department along with their average mark in maths.
List the number of students who have secured marks between 70 and 80 in physics.
mysql> select * from student;
+-----------+-------------+-------+---------+-----------+--------------+------------+-----------+
| studentNo | studentName | maths | physics | chemistry | cProgramming | department |
address |
+-----------+-------------+-------+---------+-----------+--------------+------------+-----------+
| 100 | Hari | 50 | 60 | 45 | 75 | CSE | Kasaragod |
| 101 | Devi | 60 | 55 | 78 | 40 | CSE | Kasaragod |
| 102 | Sam | 45 | 77 | 88 | 45 | IT | Kannur |
| 103 | SreeHari | 90 | 75 | 77 | 60 | IT | Calicut |
| 104 | Rani | 91 | 98 | 89 | 52 | ECE | Kannur |
| 105 | Raj | 88 | 77 | 67 | 48 | CSE | Palakkad |
+-----------+-------------+-------+---------+-----------+--------------+------------+-----------+
6 rows in set (0.00 sec)
- List all unique extensions of the files in the file server.
- Write the SQL Query to display the user ids, file extensions and count of files in each extensions.
- Identify all files created after seven days or before seven days from 15-Aug-2024;
mysql> select * from file_server;
+---------+--------------+-------------+
| user_id | file_name | create_date |
+---------+--------------+-------------+
| 1 | debug.txt | 2024-06-01 |
| 1 | exp.txt | 2024-07-01 |
| 1 | string.java | 2024-07-10 |
| 2 | add.c | 2024-08-01 |
| 2 | sub.asm | 2024-08-09 |
| 2 | sub2.Asm | 2024-08-14 |
| 3 | even.c | 2024-08-18 |
| 3 | fibonacci.c | 2024-08-12 |
| 3 | Hello.txt | 2024-08-27 |
| 3 | traffic.java | 2024-08-09 |
| 4 | EMP.TXT | 2024-08-21 |
| 4 | employee.txt | 2024-08-19 |
+---------+--------------+-------------+
