MYSQL

Q1. Consider the table STORE:

Table : Store

StoreId    Name               Location          City       NoOfEmp        DateOpened   

S101      Planetfashion       KarolBagh         Delhi        7             2015-10-16

S102      Trends              NehruNagar        Mumbai       11            2015-08-09

S103      Vogue               Vikas Vihar       Delhi        10            2015-06-27

S104      Superfashion        DefenceColony     Delhi        8             2015-02-18

S105      Rage                Bandra            Mumbai       5             2015-09-22         

Write SQL commands for the following:

a. To create the above table: Store and insert all five records in it.

ANSWER :

create database dataframe1;

use dataframe1;

create table Store(StoreId varchar(20) primary key,Name char(20),Location

insert into Store

values('S101','Planetfashion','Karol Bagh','Delhi',7,'2015/10/16',300000),

('S102','Trends','Nehru Nagar','Mumbai',11,'2015/08/09',400000),
('S103','Vogue','Vikas Vihar','Delhi',10,'2015/06/27',200000),

('S104','Superfashion','Defence Colony','Delhi',8,'2015/02/18',450000),

('S105','Rage','Bandra','Mumbai',5,'2015/09/22',600000);

select*from Store;

OUTPUT:

b. To display name, location, city, SalesAmount of stores in descending order of

ANSWER:

select Name,Location,City,SalesAmount from Store order by SalesAmount desc;

OUTPUT:
 c. To display names of stores along with SalesAmount of those stores that have
‘fashion’ anywhere in their store names.

ANSWER:

select Name,SalesAmount from Store where Name like '%fashion%';

OUTPUT:

d. To display Stores names, Location and Date Opened of stores that were opened
before 1st March, 2015.

ANSWER:

select Name,Location,DateOpened from Store where DateOpened<'2015/03/01';

e. To display total SalesAmount of each city along with city name.

ANSWER:

select City,sum(SalesAmount) from Store group by City

OUTPUT:
 f. To add a new column YearOfSales.

ANSWER:

alter table Store add YearOfSales int;

select*from Store;

OUTPUT:

To delete the column NoOfEmp.

ANSWER:

alter table Store drop column NoOfEmp;

select*from Store;

OUTPUT:
 g. To display store names ending with “ion”.

ANSWER:

select Name from Store where Name like '%ion';

OUTPUT:

To increase the SalesAmount by 10% where city is “Delhi”.

ANSWER:

update Store set SalesAmount=SalesAmount+100 where City='Delhi';

select*from Store;

OUTPUT:

h. To delete records where SalesAmount<250000.

ANSWER:

delete from Store where SalesAmount<250000;

select*from Store;

OUTPUT:
 

Q2. In a Database, there are two tables given below :

                            Table : EMPLOYEE

EMPLOYEEID     NAME                     SALES         JOBID

E1             SAMIT SINHA              1100000        102

E2             VIJAY SINGH TOMAR        1300000        101

E3             AJAY RAJPAL              1400000        103

E4             MOHIT RAMNANI            1250000        102

E5             SHAILJA SINGH            1450000        103

                                    Table : JOB

JOBID     JOBTITLE                   SALARY

101       President                  200000

102       Vice President             125000

103       Administration Assistant   80000

104       Accounting Manager         70000

106       Sales Manager               80000

Write SQL Queries for the following:

a. To display employee ids, names of employees, job ids with corresponding job
titles.

ANSWER:

select Employee.EmployeeID,Employee.Name,Job1.JobID,Job1.JobTitle from

OUTPUT:

b. To display names of employees, sales and corresponding job titles who have
achieved sales more than 1300000.

ANSWER:
select Employee.Name,Employee.Sales,Job1.Jobtitle from Employee,Job1 where
Employee.Sales>1300000 and Employee.JobID=Job1.JobID;

OUTPUT:

c. To display names and corresponding job titles of those employee who have
‘SINGH’ (anywhere) in their names.

ANSWER:

select Employee.Name,Job.Jobtitle from Employee,Job where Employee.Name like

OUTPUT:

d. Identify foreign key in the table EMPLOYEE.

ANSWER: JOBID

e. Write SQL command to change the JOBID to 104 of the Employee with ID as
E4 in the table ‘EMPLOYEE’.
ANSWER:

Q3. Consider a table EMPLOYEE with the following data and answer the SQL
queries:

ENO    ENAME          SALARY   AREA   BONUS       DATE OF JOIN

                                      CODE (%)

1      Raj Sinha     3000.50    36    12.00        19-07-2009

2      Udit Thakur   5000.45    48    10.00        22-03-2008

3      R. K. Sharma  3000.23    36    NULL         08-03-2007

4      Neha Yadav    8000.75    60    10.00        06-12-2008

5      Ajay Garg     2000.75    36    12.50        03-01-2010

6      Ravi Ranjan   7000.50    60    12.50        05-06-2008

7      Mohit Dua     5000.00    48     NULL        05-03-2008

a. Display records where bonus is not null.

ANSWER:

select*from Emp1 where Bonus is not null;

b. Display the maximum and minimum salary of employees.

ANSWER:

select max(Salarycode),min(Salarycode) from Emp1;

OUTPUT:

c. Display the number of employees and sum of salary of employees in in each

ANSWER:

select count(Ename),sum(Salarycode) from Emp1 group by Area;

OUTPUT:
 d. Display the names of employees in upper case and lower case.

ANSWER:

select upper(Ename),lower(Ename) from Emp1;

OUTPUT:

Display the salary of employees rounded to zero decimal places.

ANSWER:

select round(Salarycode,0) from Emp1;

OUTPUT:

e. Display the length of names of employees with date of join >01-01-2008.

ANSWER:

select length(Ename) from Emp1;

OUTPUT:
 f. Display the ename and month of date of join.

ANSWER:

select Ename,month(Date_of_Join) from Emp1;

OUTPUT:

g. Display the ename and year of date of join.

ANSWER:

select Ename,year(Date_of_Join) from Emp1;