Basic Select Queries
Ex. No. 3
AIM:
To Write Basic SQL Commands to execute the following queries in MySQL.
QUERIES:
SQL> create table class1(reg number(10),name varchar(10),dept varchar(10),city char(20)); Table
created.
SQL> insert into class1 values(®,&name,&dept,&city); Enter
value for reg: 5
Enter value for name: 'sakthi'
Enter value for dept: 'cse' Enter
value for city: 'delhi'
old 1: insert into class1 values(®,&name,&dept,&city) new
1: insert into class1 values(5,'sakthi','cse','delhi')
1 row created.
SQL> /
Enter value for reg: 7
Enter value for name: 'nisha'
Enter value for dept: 'cse' Enter
value for city: 'chennai'
old 1: insert into class1 values(®,&name,&dept,&city) new
1: insert into class1 values(7,'nisha','cse','chennai')
1 row created.
SQL> /
Enter value for reg: 1
Enter value for name: 'sharmin'
Enter value for dept: 'ece' Enter
value for city: 'gujarat'
old 1: insert into class1 values(®,&name,&dept,&city) new
1: insert into class1 values(1,'sharmin','ece','gujarat')
1 row created.
SQL> /
Enter value for reg: 21
Enter value for name: 'prerana' .
Enter value for dept: 'it'
Enter value for city: 'bangalore'
new 1: insert into class1 values(21,'prerana','it','bangalore') 1
row created.
SQL> select *from class1
2
SQL> select *from class1;
REG NAME DEPT CITY
---------- ---------- ---------- --------------------
5 sakthi cse delhi
7 nisha cse chennai
1 sharmin ece gujarat
21 prerana it bangalore
SQL> create table buy(reg number(10),item varchar(20),qty number(10)); Table
created.
SQL> insert into buy values(®,&item,&qty);
Enter value for reg: 2
Enter value for item: 'heater'
Enter value for qty: 1
old 1: insert into buy values(®,&item,&qty)
new 1: insert into buy values(2,'heater',1)
1 row created.
SQL> /
Enter value for reg: 7
Enter value for item: 'blender'
Enter value for qty: 3
old 1: insert into buy values(®,&item,&qty)
new 1: insert into buy values(7,'blender',3)
1 row created.
SQL> /
Enter value for reg: 21
Enter value for item: 'perfume'
Enter value for qty: 2
old 1: insert into buy values(®,&item,&qty)
new 1: insert into buy values(21,'perfume',2)
1 row created.
SQL> /
Enter value for reg: 5
Enter value for item: 'color pastels'
Enter value for qty: 15
old 1: insert into buy values(®,&item,&qty)
new 1: insert into buy values(5,'color pastels',15)
1 row created.
SQL> alter table class1 drop dept;
alter table class1 drop dept
SQL> alter table class1 drop column dept;
Table altered.
SQL> select *from class1;
REG NAME CITY
---------- ---------- --------------------
5 sakthi delhi
7 nisha chennai
1 sharmin gujarat
21 prerana bangalore
SQL> select *from buy;
REG ITEM QTY
---------- -------------------- ----------
2 heater 1
7 blender 3
21 perfume 2
5 color pastels 15
SQL> select reg from class1 union select reg from buy order by reg;
REG
--------
1
2
SQL> select reg from class1 intersect select reg from buy;
REG
----------
5
7
21
SQL> select reg from class1 except select reg from buy;
select reg from class1 except select reg from buy
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select count(name) from class1;
COUNT(NAME)
-----------
4
SQL> select count(reg) from buy where qty>2;
COUNT(REG)
----------
2
SQL> select sum(qty) from buy;
SUM(QTY)
----------
21
SQL> select avg(reg) from class1;
AVG(REG)
----------
8.5
SQL> select min(qty) from buy;
MIN(QTY)
----------
1
SQL> select max(reg) from class1;
MAX(REG)
----------
21
SQL> select max(reg) from buy where qty>1;
MAX(REG)
----------
21
SQL> select count(reg),name from class1 group by name;
COUNT(REG) NAME
---------- ----------
1 sharmin
1 nisha
1 sakthi
1 prerana
SQL> select name from class1 intersect select item from buy group by na,e; select
name from class1 intersect select item from buy group by na,e
SQL> select name from class1 intersect select item from buy group by name; select
name from class1 intersect select item from buy group by name
*
ERROR at line 1:
ORA-00904: "NAME": invalid identifier
SQL> select name from class1 intersect select item from buy;
no rows selected
SQL> select reg,items from buy group by reg; select
reg,items from buy group by reg
SQL> select reg,items from buy;
select reg,items from buy
SQL> select reg,item from buy group by reg;
select reg,item from buy group by reg
SQL> select reg,item from buy;
REG ITEM
---------- --------------------
2 heater
7 blender
21 perfume
5 color pastels
SQL> select reg from buy where qty>1;
REG
----------
7
21
5
SQL> select reg from buy where qty>1 group by reg;
REG
----------
21
5
7
SQL> select count(reg),name from class1;
select count(reg),name from class1
SQL> select count(reg),name from class1 group by name;
COUNT(REG) NAME
---------- ----------
1 sharmin
1 nisha
1 sakthi
1 prerana
SQL> select count(name),reg from class1 group by reg;
COUNT(NAME) REG
----------- ----------
1 1
1 21
1 5
1 7
SQL> select *from class1 order by name asc;
REG NAME CITY
---------- ---------- --------------------
7 nisha chennai
21 prerana bangalore
5 sakthi delhi
1 sharmin gujarat
SQL> select *from class1 order by name asc;
REG NAME CITY
---------- ---------- --------------------
7 nisha chennai
21 prerana bangalore
5 sakthi delhi
1 sharmin gujarat
SQL> select *from class1 group by reg;
select *from class1 group by reg
SQL> rename st1 to new;
Table renamed.
SQL> desc new
Name Null? Type
----------------------------------------- -------- --------------------------
-- NUM NUMBER(5)
NAME NOT NULL
VARCHAR2(20) AGE
NUMBER(5)
SQL> truncate table class1; Table
truncated.
SQL> truncate table buy;
Table truncated.
SQL> drop table class1;
Table dropped.
SQL> drop table buy;
Table dropped.
RESULT:
The SQL Queries were successfully executed and the output was obtained.
