Scribd
Upload
9 views9 pages

SEM-4 SQL Assignment

A new database named 'DBMS' was created, and an 'employee' table was established with various employee records. The table includes details such as department number, name, employee number, name, salary, and joining date, with six records inserted. Various queries were executed to retrieve employee data, including total employees, total salary, and formatting of joining dates.

Uploaded by

Atharva Pendse
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
9 views9 pages

SEM-4 SQL Assignment

A new database named 'DBMS' was created, and an 'employee' table was established with various employee records. The table includes details such as department number, name, employee number, name, salary, and joining date, with six records inserted. Various queries were executed to retrieve employee data, including total employees, total salary, and formatting of joining dates.

Uploaded by

Atharva Pendse
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/ 9

mysql> create database DBMS;

Query OK, 1 row affected (0.02 sec)

mysql> show databases;


+--------------------+
| Database |
+--------------------+
| assignments |
| dbms |
| information_schema |
| mysql |
| performance_schema |
| practical |
| sys |
+--------------------+
7 rows in set (0.00 sec)

mysql> use dbms;


Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> create table employee ( dept_no int , dept_name
varchar(30) , emp_no int , emp_name varchar(30) ,
emp_salary int , joining_date date );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into employee values(20,'Full Stack


Developer',1,'a',50000,'2023-01-03');
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee


values(21,'Finance',2,'b',40000,'2023-01-12');
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee


values(19,'Marketing',3,'c',40000,'2023-12-01');
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee


values(18,'Sales',4,'d',30000,'2023-12-22');
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee


values(17,'Transport',5,'e',35000,'2023-12-25');
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee values(16,'Cost Management


Accountant',6,'f',51000,'2023-01-03');
Query OK, 1 row affected (0.01 sec)

mysql> select * from employee;


+---------+----------------------------+--------+-----------+------------
+--------------+
| dept_no | dept_name | emp_no | emp_name |
emp_salary | joining_date |
+---------+----------------------------+--------+-----------+------------
+--------------+
| 20 | Full Stack Developer | 1 |a | 50000 |
2023-01-03 |
| 21 | Finance | 2 |b | 40000 | 2023-01-
12 |
| 19 | Marketing | 3 |c | 40000 | 2023-
12-01 |
| 18 | Sales | 4 |d | 30000 | 2023-12-22
|
| 17 | Transport | 5 |e | 35000 | 2023-12-
25 |
| 16 | Cost Management Accountant | 6|f| 51000
| 2023-01-03 |
+---------+----------------------------+--------+-----------+------------
+--------------+
6 rows in set (0.00 sec)

mysql> select * from employee where dept_no = 20;


+---------+----------------------+--------+----------+------------
+--------------+
| dept_no | dept_name | emp_no | emp_name |
emp_salary | joining_date |
+---------+----------------------+--------+----------+------------
+--------------+
| 20 | Full Stack Developer | 1|a | 50000 | 2023-
01-03 |
+---------+----------------------+--------+----------+------------
+--------------+
1 row in set (0.00 sec)

mysql> select * from employee where dept_name ='clerk';


Empty set (0.00 sec)

mysql> select * from employee order by emp_salary desc;


+---------+----------------------------+--------+-----------+------------
+--------------+
| dept_no | dept_name | emp_no | emp_name |
emp_salary | joining_date |
+---------+----------------------------+--------+-----------+------------
+--------------+
| 16 | Cost Management Accountant | 6 | Shreyash |
51000 | 2023-01-03 |
| 20 | Full Stack Developer | 1|a | 50000 |
2023-01-03 |
| 21 | Finance | 2 |b | 40000 | 2023-01-
12 |
| 19 | Marketing | 3 |c | 40000 | 2023-
12-01 |
| 17 | Transport | 5 |e | 35000 | 2023-12-
25 |
| 18 | Sales | 4 |f | 30000 | 2023-12-22
|
+---------+----------------------------+--------+-----------+------------
+--------------+
6 rows in set (0.00 sec)

mysql> select * from employee where emp_name ='%n';


Empty set (0.00 sec)
mysql> select count(emp_no) as Total_Employee from
employee;
+----------------+
| Total_Employee |
+----------------+
| 6|
+----------------+
1 row in set (0.01 sec)

mysql> select sum(emp_salary) as Total_Salary from


employee;
+--------------+
| Total_Salary |
+--------------+
| 246000 |
+--------------+
1 row in set (0.00 sec)

mysql> select max(emp_salary) as Highest_Employee_Salary


from employee;
+-------------------------+
| Highest_Employee_Salary |
+-------------------------+
| 51000 |
+-------------------------+
1 row in set (0.01 sec)
mysql> select * from employee where joining_date > 2010;
+---------+----------------------------+--------+-----------+------------
+--------------+
| dept_no | dept_name | emp_no | emp_name |
emp_salary | joining_date |
+---------+----------------------------+--------+-----------+------------
+--------------+
| 20 | Full Stack Developer | 1 |a | 50000 |
2023-01-03 |
| 21 | Finance | 2|b| 40000 | 2023-01-
12 |
| 19 | Marketing | 3 |c | 40000 | 2023-
12-01 |
| 18 | Sales | 4 |d | 30000 | 2023-12-22
|
| 17 | Transport | 5 |e | 35000 | 2023-
12-25 |
| 16 | Cost Management Accountant | 6 |f | 51000
| 2023-01-03 |
+---------+----------------------------+--------+-----------+------------
+--------------+
6 rows in set, 1 warning (0.00 sec)

mysql> select * from employee where joining_date = 01;


Empty set, 1 warning (0.00 sec)

mysql> SELECT * from employee where


MONTH(joining_date) =1;
+---------+----------------------------+--------+-----------+------------
+--------------+
| dept_no | dept_name | emp_no | emp_name |
emp_salary | joining_date |
+---------+----------------------------+--------+-----------+------------
+--------------+
| 20 | Full Stack Developer | 1 |a | 50000 |
2023-01-03 |
| 21 | Finance | 2 | b| 40000 | 2023-01-
12 |
| 16 | Cost Management Accountant | 6 |f | 51000
| 2023-01-03 |
+---------+----------------------------+--------+-----------+------------
+--------------+
3 rows in set (0.00 sec)
mysql> select dept_no, dept_name,emp_no,emp_name,
emp_salary, DATE_FORMAT(joining_date, '%d-%m-%Y') AS
formatted_joining_date from employee;
+---------+----------------------------+--------+-----------+------------
+------------------------+
| dept_no | dept_name | emp_no | emp_name |
emp_salary | formatted_joining_date |
+---------+----------------------------+--------+-----------+------------
+------------------------+
| 20 | Full Stack Developer | 1 | a| 50000 | 03-
01-2023 |
| 21 | Finance | 2 | b| 40000 | 12-01-
2023 |
| 19 | Marketing | 3 | c| 40000 | 01-12-
2023 |
| 18 | Sales | 4 | d| 30000 | 22-12-2023
|
| 17 | Transport | 5 | e| 35000 | 25-12-
2023 |
| 16 | Cost Management Accountant | 6 | f| 51000 |
03-01-2023 |
+---------+----------------------------+--------+-----------+------------
+------------------------+
6 rows in set (0.01 sec)

You might also like