IITI Command Prompt- rnysql -u root -p

1 row in set {0.00 sec)

sql> SELECT Count(e11pid) as 'No . Of Records' from EMPLOYEE ;

- - - - - - ----------+
!ID. Of Records I
------- - - -------+
B l
----- - - - - - - -----+
1 row in set {0.00 sec)

sql> SELECT Count(e11pid) as 'No . Of Records' fl'OII EMPLOYEE where city is not NULL;
---···········--+
I !<o. Of Records I
···············-+
10 I
..................
1 row in set (0.00 sec)

sql> SELECT Count(DISTINCT city) as '!<o. Of cities' fro. EMPLOYEE where city is not NULL;
.................
I !<o. Of cities I
. ................
I sI

sql> SELECT city, Count{ettpid) as 'Ho.of E11ployees' froo1 EMPLOYEE;

ERROR 1146 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated colullfl 'anu.EMPLOYEE.city'; this is inc011patible witll sql_
de-only_full_gl"Oup_by
sql> SELECT city, Count(OMipid) as 'No.of Employees· froo1 EMPLOYEE GROUP BY city;
--· ....... ·+·----------------+
I city I No.of E ■ployees I
-· ........ ·+·----------------+
3
"""'" 3
"""""
NULL 3

""'
ALLAHABAD ''
'
II '.P Type hereto search i:::li e 26-C Haze A ~Kl ~ '3~) ,.i: 4 ~ 1 13:17
',P Typeheretosearch
P Type here to search
IITI S.,o,cl:Command Prompt- rny,;ql -u rool -p
ERROR 1052. (2.3000): Colu1>n 'deptid' in field list is a1>biguous
sql> SELECT dep_ na1>e , count(empid) from EMPLOYEE,DEPART1'1ENT where EMPLOYEE.deptid-DEPARTM.ENT . deptid GROJP BY dep_name;
------ ------ ----+ -------------+
dep_ name count(eo,pid) I
Purchase 4
Sales 2.
Marketing 3
Finance 1
Human Resource 2.
. . . . . . . . . . . . . . .... --+
5 rows in set (0.00 5e,c)

ysql> SELECT dep_name , count(empid) from EMPLOYEE,DEPARTMHIT where EMPLOYEE.deptid • DEPARTMENT.deptid GROJP BY dep_name ORO!:R BY count(empid);
............... ·+·-------------+
I dep_name I count(empid) I
............... ·+·. ·------· ... ·+
I Finance I 1 I
!Sales I 2 1
I Human Resource I 2 I
I Marketing I 3I
I Purchase I 4I
+· •••••••••••••• ·+· ...•.••..... ·+
S rows in set {0.00 sec)

sql> SELECT dep_na .. e , AVG(Salary) from EMPLOYEE,OEPARTMENT where EMPLOYEE.deptid•OEPARTMENT.deptid & salary>20000 GROJP BY dep_name HAVING AVG(SALARY)>39000;
Empty set, 156 warnings (0.00 sec)

ysql> SELECT dep_na'"e , AVG(Salary) from EMPLOYEE,OEPARTMENT where EMPLOYEE.deptid•OEPARTMENT.deptlD GROUP BY dep_na,..-, HAVING AVG(SALARY)>39000 & salary>2000B;
ERROR 1054 (42522): Unknown column 'salary' in 'having clause'
ysql> SELECT dep_na,ae , AVG(Salary) from EMPLOYfE , OEPARTMENT where salary>2000 & E/o\l'LOYfLdeptid • DEPARTMENT .deptlD GROUP BY dep_name HAVING AVG(5ALAIIY)>39000 ;
Empty set, 19 warnings (0.06 sec)

sql>

·p Type here to search i:::li • 2.6-C Haze

!El Select Command Prompt rny,;ql -u root -p

ype ' help;' or "\h' for help . Type '\c' to clear the current input statement .

ysql> use anu;

ataba s e changed
y s ql> select • from STIJDENT;

RegiNo I Sname ,,. I

-------- + -------------+ ----- + -------- + -----------+ -- - - - - - +
I I City
-------- + ------------ - + ----- + -------- + -----------+ - ------ +
AdmDate Course

. -"
Ravi Trivedi Kanpur 2819-05 Java
"' Gupta "" NULL 2819-07-18 " Python I
"'
"' Kunal Sharma
Devesh Singh "" Lucknow
NULL
2019-07-18
2019-10-15
Java
Java

,"'
"' Su11it Singh
Diya llerma " NULL
Agra
Kanpur
2020-02-20
2020-03-05
Python
HTML
Nutan Ku,oar Lucknow 2020-0S-10 Python
"' Arti Soni "" Agra 2018-09-02
"'
"' Mohit Suri
Hari Dubet
NULL Kannoj
Kushi
2019-10-04
2020-02-20
"'
"'
Python
+· ••••••'"·+· ............ ·+· ...." ·+· .......
10 rows in set {8.13 sec)
·+· ••••• ----. ·+· ...... ·+

ysqb select • f rom COURSE;

+· ••• ·+· ••••••. -+- - - - - - - - - -+- - - - - - -+- - - - - - - - - - ----- -+
I CID I CNAl'IE I DURATION I FEES I Instructor
, ..
+- - - - -+- - - - - - - --+- - - - - - - - - -+- - - - - - -+- - - - - - - - - - ----- -+

"' Android
•' "'"
Kapil

.
18000 Some sh Tiwai
"' "' HTML
'' '"' Riddhi11a
,"'
"' "' Java
"'' 15000
Sumit Sansal
Raveesh Thakur

"' Python
•' 9000 Rohit Kumar

"' "'' ' 4000 Anadi Singh

+ - - - - -----+------- - - ·+· - - - - - ·+· - - - ------------+
7 rows in se t (0.08 sec)

ysql> SELECT count (Re giNo) as ' Total numb e r o-f students· from STUDHIT;
- - - - - - - - - - ------------- - - - +
Total numbe r of students
- - - - - - - - - ---------------- - +

- - - - - - --------------------+
1 row i n set {0.00 sec)
"
P Type hereto search i:::li • 26-C Haze A ~Kl ~ '31)) a: 4, IN6
IITI Command Pmmpt- mysql -u root -p

sql> SELECT CNAM.E,count(RegiNo) as 'NO. of Student · ,FEES,count(RegiNo)*FEES as 'Fees Collected' from STIJDENT,COORSE where COORSE.CNAME-STIJDENT.Course group by CNAME;
------ - -+- ---------------+------+ -------+
Cw.ME I NO. of Student I FEES I Fees Collected

Java
Python
,,,
"™'
--------+ ---------------+ ---- -+ ---------------+
rows in set (0.32 sec )

sql> SELECT CNAME,count(Regi!<o) as 'NO. of Student" from STIJDENT,COURSE where COORSE.CNAME • STIJDENT.Course group by (NAME;
········+········--------+
I Cw.ME I NO. of Student I
········+············----+
Java l
Python 4
HTML 1
SQL 2
+········+··············-·+
rows in set (0.88 sec)

ysql> SELECT Sname fr0<11 STUOENT,COURSE where COORSE.Ctul.'\E•STUOENT.Course &8 COORSE.Instructor•'Anadi Singh' group by CtJAME;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'anu.STLOENT.Sna01e' which is not functionally dependent on
columns in GROUP BY clause; this is inconrpatible with sql_OIOde•only_full_group_by
ysql> SELECT Sname froa, STUOENT,COURSE where COURSE.CNA'IE•5TUOENT.Course && COURSE.Instructor•'Anadi Singh' j
············+
I Sname
············+
I Arti Soni I
I Mohit Suri I
------------+
2 rows in set, 1 warning (0 03 sec)

sql> SELECT count(RegiNo) as 'No. of students with age above 25' from STIJDEITT where Age>25;
- - - - - - - - - - - - -----------------------+
No. of students with age above 25
----- - - - - - - ------------------------+

'
i:::li • 26-C Haze
IITI Command Prompt- mysql -u root -p
sql> SELECT Sname f...,. ST\JDENT,COURSE where COURSE.(Nl!ME-STI.OENT.Course && COURSE.Instructor-"Anadi Singh";

Sname
-------- - ---+
Arti Soni I
Mohit Suri J
------------+
2 rows in set, 1 warning (9.03 sec)

sql> SELECT count(RegiNo) as "Uo. of students with age abo,,e 2S" from STI.OENT where Age>2'i;
-· ........... ··--------------------+
I Uo. of students with age abo,,e 25 I
-· .. ....... ... ·--------------------+
, I
............ .... ·------------------+
1 row in set (9 . 06 sec)

sql> SELECT count(DISTIN(T city) as "No. of cities ' fro. STUDENT;

···············+
I Uo. of cities I
···············+
I sI
+···············+
1 row in set (9 . 08 sec)

sql> SELECT count(Reg1No) as 'Uo. of student not entering city' from STI..OENT where city.isNUL();
ERROR 1305 (42000 ): FUNCTION city.isNUL does not exist
ysql> SELECT count(RegiNo) as ' No. of student not entering city' f rom STI.OENT where city is NUL L;
................ ------------------+
I Ito. of student not entering city I
.... ......... ---------------------+
' I

sql> SELECT Course, AVG(Age) from STWENT GROUP by Course;

---- - - --+- ---------+
Course I AVG(Age ) I
--------+ ---------+
J a,,a 26.6667
Python 2'.0000

P Typeheretosearch i:::li • 26-C Haze

IITI Command Prompt- my,;ql -u mot -p
sql> SELECT count(RegiNo) as 'No. of student not entering city' from SlUlENT where city i.s NULL_;
--------------------+
No. of student not entering city I
------------------+
' I
-----------------------+
1 row in set (0.00 sec)

sql> SELECT Course, AVG(Age) from STI.OENT GROUP by Course;

---· . .. ·+·. · -------+
I Course I AVG(Age) f
-·. -· .. -+·. ·-------+
I Java I 26.6667 I
I Python I 21.0000 [
I HTML I NULL I
I SQL I 27.0000 I
········+····· -- ---+
rows in set (0 . 00 sec)

sql> SELECT CID,CNN!f ff'OII STUOENT ,COORSE where STI.OENT .Course•' Kanpur';
Empty set (0 . 00 sec)

sql> SELECT CID, CNNilf ff'OII SMENT,COORSE where STI.OENT.City•'Kanpur';

·····+·········+
I CID I ctWIE I
·····+·········+
105 Android
105 Android
107 ASP
107 ASP
103
103 "™'
101 lava"™'
101 l ava
106
10,
10,
""
""
"Y''=
10, "Y''=
10, SQC
10, SQC

II .P TypeheretDsearch i:::li e 26-C Haze A ~Kl g, '3~) ,.i: 4 ~ l l 2:2

4
IITI Command Prompt- rnysql -u mot -p X
I Diya Verma I """- "
I
I
Ravi
Diya
Trivedi
Verma
I Jaua
I
"'
'"
I
I
Ravi
Diya
Trivedi I Java
I
"''" "'
"'
I '"~
Ravi Trivedi I Java "''" ""
""
---+--------+ ----+
~· se, (0.00 sec)
'" dty , count(RegUlo) as
" sql> SELECT 'No. of sudent5" ff'OII STUOENT;
ERROR 1140 (42000): In aggregated query without GROUP BY , expression #1 of SELECT list contains nonaggregated colu,m "anu.STUOENT.City'; this is inc011Patible with sql_m
e-only_full_group_by
sql> SELECT Course, RegiNo from STUDENT where City•" Kanpur·;
----- ---+--------+
I Course I RegiNo I
--------+------- -+
I Java I 101 I
I HTML I 106 I
--------+--------+
2 rows in set (0 . 00 s ec)

sqb SELECT dty,count(Regu.lo) as ' No. of sudents ' f ro. STUDENT GROUP BY city ;
---------+- ---------------+
I city I No . of sudents I
- - - - - - - - -+- - - - - - - ---------+
Kanpu r 2
NULL 2
Lucknow 2
Agra 2
Kannoj 1
Kushi 1
---- -----+- ---------------+
6 rows in set (0.00 sec)

sql> •

',P TypeheretDsearch i:::li 0 26-C Haze A ~Kl g, '3~) ,.i 4 ~ l l 2:28

IITI Select Command Prompt - rny,;ql -u root -p
sql> SELECT dep_ nmae , AVG(salary) from EMPLOYEE,OEPARTM ENT where EMPLOYEE.deptida-OEPARTJolENT . deptid GROUP BY dep_ narae HAVING AVG(salary)>39000;

dep_ name AVG(salary)

Marketing 39333 . 3333

Kuman Resource 78500.0000

sql> SELECT ename , sala r y,dep_ name from EMPLOYEE,DEPARTMENT where EMPLOYEE.deptidzDEPARTMENT.deptid and salary>30000;

I ename
················•··· -----• ---··· ··········•
KAILASH KHER 32500 Purchase
SIJMITA ARORA 52080 Purchase
HARRY 39000 Sales
AMIT GUPTA 36000 Sa l es
GOVINO JAISWAL 43008 Marke ti ng
RAVI TIWARI 32080 Market i ng
SIJMIT srnGH 4 3008 Market i ng
BRIJESH SHUKLA 105000 Human Resource
SlJl,ltTA ARORA 52888 kuman Resource
+· •••••••••••••• · +· •••••• · +· .............. · +
9 rows in set (0.00 sec)

sql> SELECT dep_name, AVG(salary) from EMPLOYEE,OEPARTI-IENT where EMP LOYEE.deptid•DEPARTI-IEIH.deptid GROUP BY dep_na""';
················• ·············+
I dep_ name I AVG(salary) I
............. .. ·• · .. ··········+
Purchase 31625.0000
Sales 37500.0000
Marketing 39333.3333
Finance 21000.0000
Human Resource 78500 . 0000
---------------- + ------------+
S rows in set {0.00 sec)

sql> SELECT dep_name , 1>ax(AVG(salary)from EMPLOYEE,DEPARTMENT where EMPLOYEE.deptid=DEPARTMENT.deptid GROUP BY dep_name;

ERROR 1064 (42000) : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fNXII EMP LOYEE
,DEPARTMENT where EMPLOYEE.deptid =DEPARTMENT.deptid GROUP BY dep_ na' at line 1
sql> SELECT dep_ name ,1>a x(AVG(salary)from EMPLOYEE , DEPARTMENT where EMPLOYEE.deptid=DEPARTMENT deptid GROUP BY dep_narae;

P Type here to search i:::li • 26-C Haze