DBMS Lab Cycle

Experiment -1

1. Create an Employee table for the following fields

(Empno, Empname, Empaddress, salary, dept)

1. Calculate the total of the salary field

2. List the count of employees who have salary less than 50000

3. Find the details of employee having maximum salary

4. Add a new field title to the emplyee table with a default value as junior software engineer

5. Update the title of employee whose employee number 2004 to "Senor software engineer"

6. 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

1. Create Student table for the following fields:

(studentNo, studentName, maths, physics, chemistry, cProgramming, department, address)

1. List the studentno and studentname who is having marks less than 50 in C-programming in ascending order of their marks.
2. List the studentno, studentname and department, in descending order of their department and ascending order of their name.

3. List the name of students whose name starts with ‘S’.

4. Find the name of student who obtained second largest mark in in C-programming.

5. List the number of students in each department along with their average mark in maths.

6. 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)

Experiment-3

 

Experiment-4

Experiment-5

 

Experiment-6

 

Create a table file_server(user_id,file_name,create_date). First two attributes are part of the primary key.

1. List all unique extensions of the files in the file server.
2. Write the SQL Query to display the user ids, file extensions and count of files in each extensions.
3. 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  |
+---------+--------------+-------------+

Experiment-7

Question Link:

Experiment-8

Question Link: 

 

Experiment-9