DBMS SQL Queries
1. Create an Employee table and insert data into the table.
To display all database that are already created, we use
mysql> show databases;
Output:
To create a database, we use the below command:
mysql> create database Employees;
Output:
Now, to check your database is created or not, we can use the below command.
mysql> show databases;
Output:
To switched to a database
mysql>use Employees;
Output:
After that, we can create tables name “Employee” within open database “Employees”.
#Creating Employee table
create table Employee
(
Empno char(4),
Ename varchar(30),
Job varchar(20),
Hire_Date date,
Salary decimal(6,2),
Commision decimal(6,2),
Deptno char(2)
1
);
# To insert data or values in the created table 1st we have to know the data type of all those
fields of “Employee” table by execute the below command.
mysql> desc Employee;
or
mysql> describe Employee;
Output:
Inserting data into the “Employee” table.
insert into Employee
values('8369','SMITH','CLERK','1990-12-18',800.00,NULL,'20');
insert into Employee
values('8499','ANYA','SALESMAN','1991-02-20',1600,300,'30');
insert into Employee
values('8521','SETH','SALESMAN','1991-02-22',1250,500,'30');
insert into Employee
values('8554','MOHIN','SALESMAN','1991-09-28',1250,1400,'30');
insert into Employee
values('8554','BINA','MANAGER','1991-05-01',2850,NULL,'30');
insert into Employee
values('8839','AMIR','PRESIDENT','1991-11-18',5000,NULL,'10');
insert into Employee
values('8844','KULDEEP','SALESMAN','1991-09-08',1500,0,'30');
insert into Employee
values('8882','SHIVANSH','MANAGER','1991-06-09',2450,NULL,'10');
insert into Employee
values('8886','ANOOP','CLERK','1993-01-12',1100,NULL,'20');
insert into Employee
values('8888','SCOTT','ANALYST','1992-12-09',3000,NULL,'20');
2
insert into Employee
values('8900','JATIN','CLERK','1991-12-03',950,NULL,'30');
insert into Employee
values('8902','FAKIR','ANALYST','1991-12-03',3000,NULL,'20');
To check or display all records that are inserted into the table, by execute the below
command:
mysql> select * from Employee;
OUT PUT
2. To display null value in commision field to ‘No Commission’
mysql> select ename,salary, ifnull(commision,'No Commision') as commission from
employee;
OUT PUT
3. Display the employee name, job type and salary of those employee whose salary range
2000-3000.
mysql> select ename,job,salary from employee where salary between 2000 and 3000;
OUTPUT:
3
4. To display the type of job category available in the employee table.
mysql> select distinct(job) from employee;
OUTPUT:
5. Write a SQL query to display total no. of employees working in each job type.
mysql> select count(*) as ‘Department Wise’, job from employee group by job;
OUT PUT
6. Write a SQL query to display earliest and latest hire date of each department.
mysql> select min(hire_date),max(hire_date), deptno from employee group by deptno;
OUTPUT:
7. Write a SQL query to display average salary of clerk and managers of each department.
Pre-condition:- In this, first the condition is checked and only those records which
match the conditions are included in group by.
It is applied using where clause.
mysql> select avg(salary),deptno from employee where job in("clerk","manager") group by
deptno;
OUTPUT:
4
8. Write a SQL query to display the total salary of those departments where total salary is more
than seven thousand.
Post-condition: In this, first the records are grouped and after grouping the
condition is applied on the combined records and then checked.
It is applied using having clause.
mysql> select sum(salary), deptno from employee group by deptno having
sum(salary)>7000;
OUT PUT
9. Create two tables named Items, Salesperson and insert values into these tables as below:
Table: Items
create table Items
(
ITCode int not null primary key,
ItemType varchar(50),
Turnover float(10,2)
);
View the structure of the Items table
mysql> desc Items;
OUT PUT
# inserting records in the Items table.
insert into Items
values(15,'Sationary',3400000);
insert into Items
values(17,'Hosiery',6500000);
insert into Items
values(12,'Bakery',7500000);
OUT PUT
5
Table: salesperson
create table salesperson
(
Code int not null primary key,
Name varchar(50),
Salary float(6,2),
ITCode int,
foreign key (ITCode)references items (ITCode)
);
View the structure of the Salesperson table
mysql> desc Salesperson;
OUT PUT
# inserting records in the salesperson table.
insert into salesperson
values(1001,'Tandeep Jha',6000,12);
insert into salesperson
values(1002,'Yograj Sinha',7000,15);
insert into salesperson
values(1003,'Rahul Raj',4500,12);
insert into salesperson
values(1004,'Anokhi Raj',5000,17);
insert into salesperson
values(1005,'Tarana Sen',5500,17);
View all records from the salesperson table, use the below code
mysql> select * from salesperson;
OUT PUT
6
10. Write a query to display ITCode, Code, Turnover from both salesperson and Items table.
mysql> select salesperson.itcode,code,turnover from salesperson, items
where salesperson.itcode=items.itcode;
OUT PUT
11. Integrate MySQL with Python to insert data in the database’s table Student.
mysql> desc student;
OUT PUT
import mysql.connector as c
con=c.connect(host="localhost", user="root",passwd="123456789",database="om_shanti")
cursor=con.cursor()
name=input("Enter student name:")
class1=input("Enter Class")
mark=int(input("Enter mark"))
query="insert into student values('{}','{}',{})".format(name,class1,mark)
cursor.execute(query)
con.commit()
print("Data inserted successfully")
OUTPUT:
Enter student name:Mohit Kumar
Enter Class:XII
Enter mark:97
Data inserted successfully
7
# Display all records from student table after inserting the data.
mysql> select * from student;
OUT PUT
12. Integrate MySQL with Python to update data in the database’s table Student.
import mysql.connector as c
con=c.connect(host="localhost", user="root",passwd="123456789",database="om_shanti")
cursor=con.cursor()
name=input("Enter student name:")
mark=int(input("Enter New Mark:"))
query="update student set Mark={} where S_name='{}'".format(mark,name)
cursor.execute(query)
con.commit()
print("Data update successfully")
OUTPUT:
Enter student name:ram
Enter New Mark:77
Data update successfully
# Display all records from student table after updating the data.
mysql> select * from student;
OUT PUT
13. Code to delete record from the student table.
import mysql.connector as c
con=c.connect(host="localhost", user="root",passwd="123456789",database="om_shanti")
cursor=con.cursor()
name=input("Enter student name:")
query="Delete from student where s_name=('{}')".format(name)
8
cursor.execute(query)
con.commit()
print("Data deleted successfully")
OUT PUT
Enter student name:raju
Data deleted successfully
# Display all records from student table after deleting the data.
mysql> select * from student;
14. Code to display record from the salesperson table based on the Code enter by the user.
# import required modules
import mysql.connector as c
# connect python with mysql
con=c.connect(host="localhost",user="root",Password="123456789",database="Employees"
)
# get cursor object
cursor= con.cursor()
# take input from user
data=input("Enter the code:")
# Create a query
qry="SELECT * FROM salesperson where code='{}'".format(data)
# execute your query
cursor.execute(qry)
# fetch all the matching rows
result = cursor.fetchall()
# loop through the rows
if result:
print("Record Found…")
for row in result:
print(row)
print("\n")
else:
print("No record found with the given CODE.")
OUTPUT:
Enter the code:1003
Record Found…
(1003, 'Rahul Raj', 4500.0, 12)
9
