SOLUTIONS TO FLIP SQL ASSIGNMENT

Solutions to flip SQL assignment

1.a.select deptno,dname,loc from dept;

b.select * name from emp;

c.select ename,hiredate,sal*12 from emp;

d.select distinct deptno from emp;

e.select ename,hiredate from emp where sal IS NOT NULL;

f.select name,age from emp order by name;

g.select name,dept from emp where dept=30;

h.select name ,age from emp where dept='sales' order by name;

i.create table employee (eno integer,name varchar(25),salary decimal(7,2),dob date);

j.insert into employees values(20,"Kellington",4569.00,"1980-02-07");

2.a.insert into sales values(1,"Rajat","north",0,4000.00,98989878);

b.insert into sales values(2,"Manpreet","south",2000000,459.22, “9810023450”);

c.select * from sales where area="north" or area="south";

d.select area,sale from sales where sale>9000 and sale <40000;

e.select s_name from sales where commission between 200 and 400;

f.select * from sales where commission>900.00;

g.select area,sum (sale) from sales group by area;

h.select avg (sale) from sales;

i.select area from sales order by salesman_no desc;

j.update sales set sale=sale+0.10;

k.alter table sales modify mobileno integer;

l.select count (*)from sales

FLIP QUESTION ON SQL

Q.1. Correct  the following erroneous SQL commands.

a. Display deptno, dname, loc FROM dept;

b. SELECT *, name FROM emp;

c. SELECT ename, hiredate, sal*12 in “ Total Sal”  FROM emp;

d. SELECT DISTINCT( deptno) FROM emp;

e. SELECT ename, hiredate  FROM emp where sal is not null;

f. SELECT name, age ORDER BY name from emp;

g. SELECT name and dept from emp where dept=30;

h. SELECT name, age from emp ORDER BY name where dept=’sales’;

i. create employee values( e no integer, name char, salary char(20), dob date(20));

j. insert into employee( 20, “Kellington”, 4569, ‘1980-2-7’);

Q. 2. Consider the following table structure(sales) :

                 salesman_no                    integer

                s_name                                                char(15)

                area                                       char(8)

                sale                                        float

                commission                        decimal(6,2)

                mobileNo                            char(10)

correct the queries:

a.       Insert into sales values( “north”, 4000.00, 0, 1, “Rajat”,” 98989878”);

b.      Insert into sales values(2,“Manpreet”,”south Anarkali”, 2000000.00, 4569.22, 9810023450);

c.       Select * from sales where area =”North” and area=” south”;

d.      Select area, sale from sales where sales>9000 and <40000;

e.      Select s_name from sales where commission between 200 to 400;

f.        Select * from sales where commission>9000;

g.       Select sum(sale) from sales group by area;

h.      Select average sale  from sales;

i.         Select area from sales order by desc;

j.        Update sales add 0.10 in sale;

k.       Alter table sales modify mobileno;

l.         Select count (all) from sales;

m.    Delete mobileno from sales where commission>90;