SQL Practice

SQL PRACTICE:
mysql> use podar3;

Database changed
mysql> create table personal_demo(Stud_id varchar(4),Sname
varchar(20),Gender varchar(4),Address varchar(15),Email
varchar(15),Phone int,primary key(Stud_id));
Query OK, 0 rows affected (0.18 sec)
mysql> load data infile 'C:\\wamp64\\tmp\\personal_demo'

-> into table personal_demo
-> fields terminated by ','
-> ignore 1 rows;
ERROR 29 (HY000): File 'C:\wamp64\tmp\personal_demo' not found
(OS errno 2 - No such file or directory)
mysql> load data infile 'C:\\wamp64\\tmp\\personal_demo.csv'
-> ignore 1 rows;
Query OK, 10 rows affected, 25 warnings (0.01 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 25
mysql> select * from personal_demo;

+---------+--------------------+--------+----------+-----------------+------------+
| Stud_id | Sname | Gender | Address | Email | Phone
|
+---------+--------------------+--------+----------+-----------------+------------+
| S001 | Demanshu Chaudhari | Male | Dombivli |
demanshuchaudha | 2147483647 |
| S002 | Pranav Kankekar | Male | Worli | pranavkankekar0 |
2147483647 |
| S003 | Neeraj Veldandi | Male | Kurla | neerajveldandi0 |
2147483647 |
| S004 | Aaryan Raut | Male | Dadar | aaryanraut09@gm |
2147483647 |
| S005 | Mayank Kale | Male | Dadar | mayankkale02@gm |
2147483647 |
| S006 | Manushi Desai | Fema | Kalyan | manushidesai05@ |
2147483647 |
| S007 | Ritika Singh | Fema | Thane | ritikasingh03@g |
2147483647 |
| S008 | Vaishnavi Shetty | Fema | Parel | vaishnavishetty |
2147483647 |
| S009 | Swarangi Kumbhar | Fema | Vikhroli | swarangikumbhar
| 2147483647 |
| S010 | Ragini | Fema | Vadala | raginiragini04@ |
2147483647 |
+---------+--------------------+--------+----------+-----------------+------------+
10 rows in set (0.14 sec)
mysql> ALTER TABLE personal_demo

-> UPDATE gender varchar(6);
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 'UPDATE gender varchar(6)' at line 2
mysql> ALTER TABLE personaL_demo
-> UPDATE Gender varchar(6);
right syntax to use near 'UPDATE Gender varchar(6)' at line 2
-> UPDATE Gender varchar(6);
right syntax to use near 'UPDATE Gender varchar(6)' at line 2
-> MODIFY Gender varchar(6);
Records: 10 Duplicates: 0 Warnings: 0

-> MODIFY Email varchar(30);

+---------+--------------------+--------+----------+-----------------+------------+
| Stud_id | Sname | Gender | Address | Email | Phone
|
+---------+--------------------+--------+----------+-----------------+------------+
demanshuchaudha | 2147483647 |
| S002 | Pranav Kankekar | Male | Worli | pranavkankekar0 |
2147483647 |
| S003 | Neeraj Veldandi | Male | Kurla | neerajveldandi0 |
2147483647 |
| S004 | Aaryan Raut | Male | Dadar | aaryanraut09@gm |
2147483647 |
| S005 | Mayank Kale | Male | Dadar | mayankkale02@gm |
2147483647 |
| S006 | Manushi Desai | Fema | Kalyan | manushidesai05@ |
2147483647 |
| S007 | Ritika Singh | Fema | Thane | ritikasingh03@g |
2147483647 |
| S008 | Vaishnavi Shetty | Fema | Parel | vaishnavishetty |
2147483647 |
| S009 | Swarangi Kumbhar | Fema | Vikhroli | swarangikumbhar
| 2147483647 |
| S010 | Ragini | Fema | Vadala | raginiragini04@ |
2147483647 |
+---------+--------------------+--------+----------+-----------------+------------+
mysql> drop databse podar3;
right syntax to use near 'databse podar3' at line 1
mysql> drop podar3;
right syntax to use near 'podar3' at line 1
mysql> DROP podar3;
right syntax to use near 'podar3' at line 1
mysql> DROP DATABASE podar3;
Query OK, 1 row affected (0.15 sec)
mysql> create database podar3;

Query OK, 1 row affected (0.19 sec)
mysql> use podar3;

Database changed
mysql> create table personal_demo(Stud_id varchar(4),Sname
varchar(20),Gender varchar(6),Address varchar(15),Email
varchar(30),Phone varchar(10),primary key(Stud_id));
mysql> load data infile 'C:\\wamp64\\tmp\\personal_demo.csv'

-> ignore 1 rows;

+---------+--------------------+--------+----------+-------------------------------+----
--------+
| Stud_id | Sname | Gender | Address | Email |
Phone |
+---------+--------------------+--------+----------+-------------------------------+----
--------+
demanshuchaudhari27@gmail.com | 7715907377 |
| S002 | Pranav Kankekar | Male | Worli |
pranavkankekar04@gmail.com | 9892395141 |
| S003 | Neeraj Veldandi | Male | Kurla |
neerajveldandi08@gmail.com | 7021447090 |
| S004 | Aaryan Raut | Male | Dadar |
aaryanraut09@gmail.com | 8369156737 |
| S005 | Mayank Kale | Male | Dadar |
mayankkale02@gmail.com | 8657272369 |
| S006 | Manushi Desai | Female | Kalyan |
manushidesai05@gmail.com | 9136959310 |
| S007 | Ritika Singh | Female | Thane |
ritikasingh03@gmail.com | 8779961108 |
| S008 | Vaishnavi Shetty | Female | Parel |
vaishnavishetty23@gmail.com | 9819519604 |
| S009 | Swarangi Kumbhar | Female | Vikhroli |
swarangikumbhar01@gmail.com | 9594745656 |
| S010 | Ragini | Female | Vadala |
raginiragini04@gmail.com | 8104162336 |
+---------+--------------------+--------+----------+-------------------------------+----
--------+
mysql> create table student_demo(Stud_id

varchar(4),Student_Name varchar(40),Semester int,Subject_1
int,Subject_2 int,Subject_3 int,Subject_4 int,Subject_5 int,foreign
key(Stud_id) references personal_demo(Stud_id));
mysql> load data infile 'C:\\wamp64\\tmp\\student_demo.csv'

-> into table student_demo
-> ignore 1 rows;
mysql> select * from student_demo;

+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+
| Stud_id | Student_Name | Semester | Subject_1 | Subject_2 |
Subject_3 | Subject_4 | Subject_5 |
+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+
| S001 | Demanshu Chaudhari | 1| 69 | 55 | 59 |
75 | 91 |
59 | 73 |
95 | 92 |
61 | 54 |
| S002 | Pranav Kankekar | 1| 71 | 78 | 55 |
93 | 54 |
82 | 94 |
55 | 63 |
81 | 85 |
| S003 | Neeraj Veldandi | 1| 59 | 90 | 90 |
95 | 95 |
55 | 61 |
98 | 69 |
97 | 62 |
| S004 | Aaryan Raut | 1| 50 | 51 | 84 | 65
| 89 |
| S004 | Aaryan Raut | 2| 71 | 53 | 58 | 95
| 72 |
| S004 | Aaryan Raut | 3| 75 | 89 | 87 | 78
| 56 |
| S004 | Aaryan Raut | 4| 66 | 63 | 97 | 82
| 55 |
| S005 | Mayank Kale | 1| 79 | 92 | 63 | 93
| 92 |
| S005 | Mayank Kale | 2| 87 | 73 | 53 | 82
| 72 |
| S005 | Mayank Kale | 3| 87 | 52 | 68 | 68
| 88 |
| S005 | Mayank Kale | 4| 85 | 95 | 63 | 63
| 72 |
| S006 | Manushi Desai | 1| 55 | 56 | 89 |
86 | 75 |
| S006 | Manushi Desai | 2| 57 | 89 | 98 |
53 | 58 |
| S006 | Manushi Desai | 3| 67 | 64 | 69 |
74 | 68 |
| S006 | Manushi Desai | 4| 70 | 73 | 77 |
75 | 84 |
| S007 | Ritika Singh | 1| 59 | 83 | 71 | 91 |
97 |
| S007 | Ritika Singh | 2| 58 | 91 | 53 | 81 |
77 |
| S007 | Ritika Singh | 3| 62 | 69 | 81 | 74 |
81 |
| S007 | Ritika Singh | 4| 89 | 95 | 57 | 87 |
65 |
| S008 | Vaishnavi Shetty | 1| 64 | 50 | 73 |
62 | 92 |
83 | 57 |
55 | 89 |
60 | 76 |
| S009 | Swarangi Kumbhar | 1| 81 | 90 | 96 |
50 | 99 |
85 | 86 |
77 | 52 |
92 | 54 |
| S010 | Ragini | 1| 91 | 57 | 65 | 65 |
56 |
| S010 | Ragini | 2| 65 | 65 | 69 | 85 |
95 |
| S010 | Ragini | 3| 79 | 69 | 85 | 95 |
87 |
| S010 | Ragini | 4| 75 | 97 | 71 | 81 |
71 |
+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+
mysql> desc personal_demo;

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Stud_id | varchar(4) | NO | PRI | NULL | |
| Sname | varchar(20) | YES | | NULL | |
| Gender | varchar(6) | YES | | NULL | |
| Address | varchar(15) | YES | | NULL | |
| Email | varchar(30) | YES | | NULL | |
| Phone | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
mysql> select
personal_demo.Stud_id,personal_demo.Sname,personal_demo.Gen
der,personal_demo.Address,personal_demo.Email,personal_demo.P
hone,student_demo.Stud_id,student_demo.Student_Name,student_
demo.Semester,student_demo.Subject_1,student_demo.Subject_2,s
tudent_demo.Subject_3,student_demo.Subject_4,student_demo.Su
bject_5
-> FROM personal_demo
-> JOIN student_demo ON
personal_demo.Stud_id=student_demo.Stud_id;
+---------+--------------------+--------+----------+-------------------------------+----
--------+---------+--------------------+----------+-----------+-----------+-----------
+-----------+-----------+
Phone | Stud_id | Student_Name | Semester | Subject_1 |
Subject_2 | Subject_3 | Subject_4 | Subject_5 |
+---------+--------------------+--------+----------+-------------------------------+----
--------+---------+--------------------+----------+-----------+-----------+-----------
+-----------+-----------+
demanshuchaudhari27@gmail.com | 7715907377 | S001 |
Demanshu Chaudhari | 1| 69 | 55 | 59 | 75 |
91 |
73 |
92 |
54 |
pranavkankekar04@gmail.com | 9892395141 | S002 | Pranav
Kankekar | 1| 71 | 78 | 55 | 93 | 54 |
Kankekar | 2| 68 | 51 | 93 | 82 | 94 |
Kankekar | 3| 66 | 96 | 54 | 55 | 63 |
Kankekar | 4| 68 | 53 | 84 | 81 | 85 |
neerajveldandi08@gmail.com | 7021447090 | S003 | Neeraj
Veldandi | 1| 59 | 90 | 90 | 95 | 95 |
Veldandi | 2| 54 | 62 | 89 | 55 | 61 |
Veldandi | 3| 98 | 98 | 60 | 98 | 69 |
Veldandi | 4| 93 | 74 | 70 | 97 | 62 |
aaryanraut09@gmail.com | 8369156737 | S004 | Aaryan Raut
| 1| 50 | 51 | 84 | 65 | 89 |
| 2| 71 | 53 | 58 | 95 | 72 |
| 3| 75 | 89 | 87 | 78 | 56 |
| 4| 66 | 63 | 97 | 82 | 55 |
mayankkale02@gmail.com | 8657272369 | S005 | Mayank
Kale | 1| 79 | 92 | 63 | 93 | 92 |
Kale | 2| 87 | 73 | 53 | 82 | 72 |
Kale | 3| 87 | 52 | 68 | 68 | 88 |
Kale | 4| 85 | 95 | 63 | 63 | 72 |
manushidesai05@gmail.com | 9136959310 | S006 | Manushi
Desai | 1| 55 | 56 | 89 | 86 | 75 |
Desai | 2| 57 | 89 | 98 | 53 | 58 |
Desai | 3| 67 | 64 | 69 | 74 | 68 |
Desai | 4| 70 | 73 | 77 | 75 | 84 |
ritikasingh03@gmail.com | 8779961108 | S007 | Ritika Singh
| 1| 59 | 83 | 71 | 91 | 97 |
| 2| 58 | 91 | 53 | 81 | 77 |
| 3| 62 | 69 | 81 | 74 | 81 |
| 4| 89 | 95 | 57 | 87 | 65 |
vaishnavishetty23@gmail.com | 9819519604 | S008 | Vaishnavi
Shetty | 1| 64 | 50 | 73 | 62 | 92 |
Shetty | 2| 68 | 74 | 70 | 83 | 57 |
Shetty | 3| 61 | 57 | 64 | 55 | 89 |
Shetty | 4| 98 | 84 | 88 | 60 | 76 |
swarangikumbhar01@gmail.com | 9594745656 | S009 | Swarangi
Kumbhar | 1| 81 | 90 | 96 | 50 | 99 |
Kumbhar | 2| 68 | 69 | 67 | 85 | 86 |
Kumbhar | 3| 62 | 65 | 57 | 77 | 52 |
Kumbhar | 4| 58 | 67 | 58 | 92 | 54 |
raginiragini04@gmail.com | 8104162336 | S010 | Ragini |
1| 91 | 57 | 65 | 65 | 56 |
2| 65 | 65 | 69 | 85 | 95 |
3| 79 | 69 | 85 | 95 | 87 |
4| 75 | 97 | 71 | 81 | 71 |
+---------+--------------------+--------+----------+-------------------------------+----
--------+---------+--------------------+----------+-----------+-----------+-----------
+-----------+-----------+
mysql> ALTER TABLE student_demo
-> MODIFY total int;
ERROR 1054 (42S22): Unknown column 'total' in 'student_demo'
mysql> ALTER TABLE student_demo
-> ADD total int;
mysql> UPDATE student_demo

-> SET
table=Subject_1+Subject_2+Subject_3+Subject_4+Subject_5;
right syntax to use near
'table=Subject_1+Subject_2+Subject_3+Subject_4+Subject_5' at line
2
mysql> UPDATE student_demo
-> SET
total=Subject_1+Subject_2+Subject_3+Subject_4+Subject_5;
Rows matched: 40 Changed: 40 Warnings: 0

+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+-------+
Subject_3 | Subject_4 | Subject_5 | total |
+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+-------+
75 | 91 | 349 |
59 | 73 | 333 |
95 | 92 | 395 |
61 | 54 | 316 |
93 | 54 | 351 |
82 | 94 | 388 |
55 | 63 | 334 |
81 | 85 | 371 |
95 | 95 | 429 |
55 | 61 | 321 |
98 | 69 | 423 |
97 | 62 | 396 |
| S004 | Aaryan Raut | 1| 50 | 51 | 84 | 65
| 89 | 339 |
| S004 | Aaryan Raut | 2| 71 | 53 | 58 | 95
| 72 | 349 |
| S004 | Aaryan Raut | 3| 75 | 89 | 87 | 78
| 56 | 385 |
| S004 | Aaryan Raut | 4| 66 | 63 | 97 | 82
| 55 | 363 |
| S005 | Mayank Kale | 1| 79 | 92 | 63 | 93
| 92 | 419 |
| S005 | Mayank Kale | 2| 87 | 73 | 53 | 82
| 72 | 367 |
| S005 | Mayank Kale | 3| 87 | 52 | 68 | 68
| 88 | 363 |
| S005 | Mayank Kale | 4| 85 | 95 | 63 | 63
| 72 | 378 |
| S006 | Manushi Desai | 1| 55 | 56 | 89 |
86 | 75 | 361 |
| S006 | Manushi Desai | 2| 57 | 89 | 98 |
53 | 58 | 355 |
| S006 | Manushi Desai | 3| 67 | 64 | 69 |
74 | 68 | 342 |
| S006 | Manushi Desai | 4| 70 | 73 | 77 |
75 | 84 | 379 |
| S007 | Ritika Singh | 1| 59 | 83 | 71 | 91 |
97 | 401 |
| S007 | Ritika Singh | 2| 58 | 91 | 53 | 81 |
77 | 360 |
| S007 | Ritika Singh | 3| 62 | 69 | 81 | 74 |
81 | 367 |
| S007 | Ritika Singh | 4| 89 | 95 | 57 | 87 |
65 | 393 |
62 | 92 | 341 |
83 | 57 | 352 |
55 | 89 | 326 |
60 | 76 | 406 |
50 | 99 | 416 |
85 | 86 | 375 |
77 | 52 | 313 |
92 | 54 | 329 |
| S010 | Ragini | 1| 91 | 57 | 65 | 65 |
56 | 334 |
| S010 | Ragini | 2| 65 | 65 | 69 | 85 |
95 | 379 |
| S010 | Ragini | 3| 79 | 69 | 85 | 95 |
87 | 415 |
| S010 | Ragini | 4| 75 | 97 | 71 | 81 |
71 | 395 |
+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+-------+
mysql> select Student_Name,Semester

-> FROM Student_demo
-> where Semester=1;
+--------------------+----------+
| Student_Name | Semester |
+--------------------+----------+
| Demanshu Chaudhari | 1|
| Pranav Kankekar | 1|
| Neeraj Veldandi | 1|
| Aaryan Raut | 1|
| Mayank Kale | 1|
| Manushi Desai | 1|
| Ritika Singh | 1|
| Vaishnavi Shetty | 1|
| Swarangi Kumbhar | 1|
| Ragini | 1|
+--------------------+----------+
mysql> select Student_Name,Semester ,total

-> where Semester=1;
+--------------------+----------+-------+
| Student_Name | Semester | total |
+--------------------+----------+-------+
| Demanshu Chaudhari | 1 | 349 |
| Pranav Kankekar | 1 | 351 |
| Neeraj Veldandi | 1 | 429 |
| Aaryan Raut | 1 | 339 |
| Mayank Kale | 1 | 419 |
| Manushi Desai | 1 | 361 |
| Ritika Singh | 1 | 401 |
| Vaishnavi Shetty | 1 | 341 |
| Swarangi Kumbhar | 1 | 416 |
| Ragini | 1 | 334 |
+--------------------+----------+-------+
mysql> select AVG(Semester) AS avg_semester

-> GROUP BY Semester;
+--------------+
| avg_semester |
+--------------+
| 1.0000 |
| 2.0000 |
| 3.0000 |
| 4.0000 |
+--------------+
mysql> select Semester, AVG(Semester) AS avg_semester

-> ;
ERROR 1054 (42S22): Unknown column 'Semester' in 'field list'
mysql> select Semester, AVG(Subject_3) AS avg_subject_3
+----------+---------------+
| Semester | avg_subject_3 |
+----------+---------------+
| 1| 74.5000 |
| 2| 70.3000 |
| 3| 71.0000 |
| 4| 73.7000 |
+----------+---------------+

+---------+-------------+------+-----+---------+-------+
+---------+-------------+------+-----+---------+-------+
+---------+-------------+------+-----+---------+-------+
mysql> select personal_demo.Phone,Student_demo.Student_Name

-> JOIN Student_demo ON
personal_demo.Stud_id=Student_demo.Stud_id
-> ;
+------------+--------------------+
| Phone | Student_Name |
+------------+--------------------+
| 7715907377 | Demanshu Chaudhari |
| 9892395141 | Pranav Kankekar |
| 7021447090 | Neeraj Veldandi |
| 8369156737 | Aaryan Raut |
| 8369156737 | Aaryan Raut |
| 8369156737 | Aaryan Raut |
| 8369156737 | Aaryan Raut |
| 8657272369 | Mayank Kale |
| 8657272369 | Mayank Kale |
| 8657272369 | Mayank Kale |
| 8657272369 | Mayank Kale |
| 9136959310 | Manushi Desai |
| 8779961108 | Ritika Singh |
| 8779961108 | Ritika Singh |
| 8779961108 | Ritika Singh |
| 8779961108 | Ritika Singh |
| 9819519604 | Vaishnavi Shetty |
| 9594745656 | Swarangi Kumbhar |
| 8104162336 | Ragini |
| 8104162336 | Ragini |
| 8104162336 | Ragini |
| 8104162336 | Ragini |
+------------+--------------------+
mysql> select
personal_demo.Phone,Student_demo.Student_Name,Student_demo
.Subject_5
-> where Subject_5<=71;
+------------+--------------------+-----------+
| Phone | Student_Name | Subject_5 |
+------------+--------------------+-----------+
| 7715907377 | Demanshu Chaudhari | 54 |
| 9892395141 | Pranav Kankekar | 54 |
| 9892395141 | Pranav Kankekar | 63 |
| 7021447090 | Neeraj Veldandi | 61 |
| 7021447090 | Neeraj Veldandi | 69 |
| 7021447090 | Neeraj Veldandi | 62 |
| 8369156737 | Aaryan Raut | 56 |
| 8369156737 | Aaryan Raut | 55 |
| 9136959310 | Manushi Desai | 58 |
| 9136959310 | Manushi Desai | 68 |
| 8779961108 | Ritika Singh | 65 |
| 9819519604 | Vaishnavi Shetty | 57 |
| 9594745656 | Swarangi Kumbhar | 52 |
| 9594745656 | Swarangi Kumbhar | 54 |
| 8104162336 | Ragini | 56 |
| 8104162336 | Ragini | 71 |
+------------+--------------------+-----------+
mysql> Select
personal_demo.Gender,Student_demo.Semester,Student_demo.AV
G(Subject_1) AS avg_sub_1,Student_demo.AVG(Subject_2) AS
avg_sub_2,Student_demo.AVG(Subject_3) AS
avg_sub_3,Student_demo.AVG(Subject_4) AS
avg_sub_4,Student_demo.AVG(Subject_5) AS avg_sub_5
-> where Semester=4
-> GROUP BY Gender;
ERROR 1630 (42000): FUNCTION student_demo.AVG does not exist.
Check the 'Function Name Parsing and Resolution' section in the
Reference Manual
mysql> Select personal_demo.Gender,Student_demo.Semester,
-> AVG(Student_demo.Subject_1) AS avg_subj_1,
-> ,
-> ;
right syntax to use near ',' at line 6
-> where Semester=4
-> GROUP BY Gender;
right syntax to use near 'FROM personal_demo
JOIN Student_demo ON
personal_demo.Stud_id=Student_demo.Stud_' at line 7
-> AVG(Student_demo.Subject_5) AS avg_subj_5
-> where Semester=4
-> GROUP BY Gender;
+--------+----------+------------+------------+------------+------------+------------+
| Gender | Semester | avg_subj_1 | avg_subj_2 | avg_subj_3 |
avg_subj_4 | avg_subj_5 |
+--------+----------+------------+------------+------------+------------+------------+
| Male | 4 | 75.2000 | 70.0000 | 77.2000 | 76.8000 |
65.6000 |
| Female | 4 | 78.0000 | 83.2000 | 70.2000 | 79.0000 |
70.0000 |
+--------+----------+------------+------------+------------+------------+------------+
mysql> select * from Student_demo;

+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+-------+
+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+-------+
75 | 91 | 349 |
59 | 73 | 333 |
95 | 92 | 395 |
61 | 54 | 316 |
93 | 54 | 351 |
82 | 94 | 388 |
55 | 63 | 334 |
81 | 85 | 371 |
95 | 95 | 429 |
55 | 61 | 321 |
98 | 69 | 423 |
97 | 62 | 396 |
| S004 | Aaryan Raut | 1| 50 | 51 | 84 | 65
| 89 | 339 |
| S004 | Aaryan Raut | 2| 71 | 53 | 58 | 95
| 72 | 349 |
| S004 | Aaryan Raut | 3| 75 | 89 | 87 | 78
| 56 | 385 |
| S004 | Aaryan Raut | 4| 66 | 63 | 97 | 82
| 55 | 363 |
| S005 | Mayank Kale | 1| 79 | 92 | 63 | 93
| 92 | 419 |
| S005 | Mayank Kale | 2| 87 | 73 | 53 | 82
| 72 | 367 |
| S005 | Mayank Kale | 3| 87 | 52 | 68 | 68
| 88 | 363 |
| S005 | Mayank Kale | 4| 85 | 95 | 63 | 63
| 72 | 378 |
| S006 | Manushi Desai | 1| 55 | 56 | 89 |
86 | 75 | 361 |
| S006 | Manushi Desai | 2| 57 | 89 | 98 |
53 | 58 | 355 |
| S006 | Manushi Desai | 3| 67 | 64 | 69 |
74 | 68 | 342 |
| S006 | Manushi Desai | 4| 70 | 73 | 77 |
75 | 84 | 379 |
| S007 | Ritika Singh | 1| 59 | 83 | 71 | 91 |
97 | 401 |
| S007 | Ritika Singh | 2| 58 | 91 | 53 | 81 |
77 | 360 |
| S007 | Ritika Singh | 3| 62 | 69 | 81 | 74 |
81 | 367 |
| S007 | Ritika Singh | 4| 89 | 95 | 57 | 87 |
65 | 393 |
62 | 92 | 341 |
83 | 57 | 352 |
55 | 89 | 326 |
60 | 76 | 406 |
50 | 99 | 416 |
85 | 86 | 375 |
77 | 52 | 313 |
92 | 54 | 329 |
| S010 | Ragini | 1| 91 | 57 | 65 | 65 |
56 | 334 |
| S010 | Ragini | 2| 65 | 65 | 69 | 85 |
95 | 379 |
| S010 | Ragini | 3| 79 | 69 | 85 | 95 |
87 | 415 |
| S010 | Ragini | 4| 75 | 97 | 71 | 81 |
71 | 395 |
+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+-------+
mysql> select Student_Name,total from Student_demo where

Student_Name like 'R%';
+--------------+-------+
| Student_Name | total |
+--------------+-------+
| Ritika Singh | 401 |
| Ragini | 334 |
| Ragini | 379 |
| Ragini | 415 |
| Ragini | 395 |
+--------------+-------+
mysql> select Student_Name,Semester,total

+--------------------+----------+-------+
| Student_Name | Semester | total |
+--------------------+----------+-------+
+--------------------+----------+-------+
mysql> select Student_Name,Semester,

-> SUM(total) AS all_total
+--------------------+----------+-----------+
| Student_Name | Semester | all_total |
+--------------------+----------+-----------+
| Demanshu Chaudhari | 1| 3740 |
+--------------------+----------+-----------+
mysql> select Student_Name,

-> GROUP BY Student_Name;
+--------------------+-----------+
| Student_Name | all_total |
+--------------------+-----------+
| Demanshu Chaudhari | 1393 |
| Pranav Kankekar | 1444 |
| Neeraj Veldandi | 1569 |
| Aaryan Raut | 1436 |
| Mayank Kale | 1527 |
| Manushi Desai | 1437 |
| Vaishnavi Shetty | 1425 |
| Swarangi Kumbhar | 1433 |
| Ragini | 1523 |
+--------------------+-----------+

-> GROUP BY Student_Name DESC;
right syntax to use near 'DESC' at line 4
-> GROUP BY Student_Name DESC
-> ORDER BY all_total DESC lIMIT 3;
right syntax to use near 'DESC
ORDER BY all_total DESC lIMIT 3' at line 4
-> ORDER BY all_total DESC
-> LIMIT 3;
right syntax to use near 'DESC
ORDER BY all_total DESC
LIMIT 3' at line 4
-> ;
right syntax to use near 'DESC' at line 4
-> GROUP BY Student_Name
-> ORDER BY all_total DESC
-> LIMIT 3;
+-----------------+-----------+
| Student_Name | all_total |
+-----------------+-----------+
| Neeraj Veldandi | 1569 |
| Mayank Kale | 1527 |
| Ragini | 1523 |
+-----------------+-----------+
mysql> select Semester,

-> SUM(total) AS all_sem_total
-> FROM Student_name
ERROR 1146 (42S02): Table 'podar3.student_name' doesn't exist
mysql> select Semester
-> FROM Student_Name
right syntax to use near 'SUM(total) AS all_sem_total
FROM Student_Name
GROUP BY Semester' at line 2
+----------+---------------+
| Semester | all_sem_total |
+----------+---------------+
| 1| 3740 |
| 2| 3579 |
| 3| 3663 |
| 4| 3726 |
+----------+---------------+

-> GROUP BY Semester
-> ;
mysql> create table employee_personal(emp_id varchar(6),firstname
varchar(25),department varchar(20),primary key(emp_id));
mysql> load data infile 'C:\\wamp64\\tmp\\employee_personal.csv'

-> into table employee_personal
-> ignore 1 rows;
mysql> select * from employee_personal;

+--------+-----------+-------------+
| emp_id | firstname | department |
+--------+-----------+-------------+
|1 | Tejas | Sales
|2 | Demanshu | Sales
|3 | Pranav | Sales
|MP004 | Neeraj | Marketing
|MP005 | Ram | Marketing
|006 | Adhish | Finance
|007 | Mayank | Finance
|EMP008 | Sangam | Operations
|EMP009 | Utkarsh | Operations
|EMP010 | Vedant | Operations
| Viren | Hr
| Raghav | Hr
|3 | Otto | Admin
|4 | Tom | Admin
+--------+-----------+-------------+
mysql> select * emp_id from employee_personal;

right syntax to use near 'emp_id from employee_personal' at line 1
mysql> select emp_id from employee_personal;
+--------+
| emp_id |
+--------+
| EMP001 |
| EMP002 |
| EMP003 |
| EMP004 |
| EMP005 |
| EMP006 |
| EMP007 |
| EMP008 |
| EMP009 |
| EMP010 |
| EMP011 |
| EMP012 |
| EMP013 |
| EMP014 |
+--------+
mysql> create table employee_performance(emp_id

varchar(6),Performance_rating float,foreign key(emp_id)references
employee_personal(emp_id));
mysql> load data infile

'C:\\wamp64\\tmp\\employee_performance.csv'
-> into table employee_performance
-> ignore 1 rows;
mysql> select* from employee_performance;

+--------+--------------------+
| emp_id | Performance_rating |
+--------+--------------------+
| EMP001 | 8.5 |
| EMP002 | 9.1 |
| EMP003 | 8.6 |
| EMP004 | 6.2 |
| EMP005 | 0|
| EMP006 | 7.1 |
| EMP007 | 8.3 |
| EMP008 | 5.6 |
| EMP009 | 6.5 |
| EMP010 | 10 |
| EMP011 | 2.7 |
| EMP012 | 0|
| EMP013 | 4.8 |
| EMP014 | 7.5 |
+--------+--------------------+
mysql> desc employee_personal;

+------------+-------------+------+-----+---------+-------+
+------------+-------------+------+-----+---------+-------+
| emp_id | varchar(6) | NO | PRI | NULL | |
| firstname | varchar(25) | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
mysql> select
employee_personal.department,employee_personal.department,em
ployee_personal.department,employee_performance.emp_id,emplo
yee_performance.Performance_rating
-> FROM employee_personal
-> JOIN employee_performance ON
employee_personal.emp_id=employee_performance.emp_id;
+-------------+-------------+-------------+--------+--------------------+
| department | department | department | emp_id |
Performance_rating |
+-------------+-------------+-------------+--------+--------------------+
| EMP001 | 8.5 |
| EMP002 | 9.1 |
| EMP003 | 8.6 |
| EMP004 | 6.2 |
| EMP005 | 0|
| EMP006 | 7.1 |
| EMP007 | 8.3 |
| EMP008 | 5.6 |
| EMP009 | 6.5 |
| EMP010 | 10 |
| EMP011 | 2.7 |
| EMP012 | 0|
| EMP013 | 4.8 |
| EMP014 | 7.5 |
+-------------+-------------+-------------+--------+--------------------+
mysql> select
employee_personal.emp_id,employee_personal.firstname,employee
_personal.department,employee_performance.emp_id,employee_p
erformance.Performance_rating
+--------+-----------+-------------+--------+--------------------+
| emp_id | firstname | department | emp_id | Performance_rating
|
+--------+-----------+-------------+--------+--------------------+
| EMP001 | 8.5 |
| EMP002 | 9.1 |
| EMP003 | 8.6 |
| EMP004 | 6.2 |
| EMP005 | 0|
| EMP006 | 7.1 |
| EMP007 | 8.3 |
| EMP008 | 5.6 |s
| EMP009 | 6.5 |s
| EMP010 | 10 |s
| EMP011 | 2.7 |
| EMP012 | 0|
| EMP013 | 4.8 |
| EMP014 | 7.5 |
+--------+-----------+-------------+--------+--------------------+
mysql> select employee_personal.department,

-> AVG(employee_performance.Performance_rating) AS
avg_performance
employee_personal.emp_id=employee_performance.emp_id
-> GROUP BY employee_personal.department;
+-------------+--------------------+
| department | avg_performance |
+-------------+--------------------+
| 8.733333587646484 |
| 3.0999999046325684 |
| 7.700000047683716 |
| 7.3666666348775225 |
| 1.350000023841858 |
| 6.150000095367432 |
+-------------+--------------------+
mysql> select employee_personal.firstname,

-> MAX(employee_performance.Performance_rating) AS
highest_performance
+-----------+---------------------+
| firstname | highest_performance |
+-----------+---------------------+
| Tejas | 10 |
+-----------+---------------------+
1 row in set (0.13 sec)
mysql> select * from employee_performance;

+--------+--------------------+
+--------+--------------------+
| EMP001 | 8.5 |
| EMP002 | 9.1 |
| EMP003 | 8.6 |
| EMP004 | 6.2 |
| EMP005 | 0|
| EMP006 | 7.1 |
| EMP007 | 8.3 |
| EMP008 | 5.6 |
| EMP009 | 6.5 |
| EMP010 | 10 |
| EMP011 | 2.7 |
| EMP012 | 0|
| EMP013 | 4.8 |
| EMP014 | 7.5 |
+--------+--------------------+
mysql> select * from employee_personal;

+--------+-----------+-------------+
| emp_id | firstname | department |
+--------+-----------+-------------+
|1 | Tejas | Sales
|2 | Demanshu | Sales
|3 | Pranav | Sales
|MP004 | Neeraj | Marketing
|MP005 | Ram | Marketing
|006 | Adhish | Finance
|007 | Mayank | Finance
|EMP008 | Sangam | Operations
|EMP009 | Utkarsh | Operations
|EMP010 | Vedant | Operations
| Viren | Hr
| Raghav | Hr
|3 | Otto | Admin
|4 | Tom | Admin
+--------+-----------+-------------+
mysql> select
-> employee_personal.emp_id,
-> employee_personal.firstname,
-> employee_personal.department,
-> employee_performance.emp_id,
-> employee_performance.Performance_rating,
-> ;
right syntax to use near '' at line 6
mysql> select
-> employee_personal.emp_id,
-> employee_personal.firstname,
-> employee_personal.department,
-> employee_performance.emp_id,
-> employee_performance.Performance_rating
-> FROM
-> employee_personal
-> JOIN
-> employee_performance ON
+--------+-----------+-------------+--------+--------------------+
| emp_id | firstname | department | emp_id | Performance_rating
|
+--------+-----------+-------------+--------+--------------------+
| EMP001 | 8.5 |
| EMP002 | 9.1 |
| EMP003 | 8.6 |
| EMP004 | 6.2 |
| EMP005 | 0|
| EMP006 | 7.1 |
| EMP007 | 8.3 |
| EMP008 | 5.6 |s
| EMP009 | 6.5 |s
| EMP010 | 10 |s
| EMP011 | 2.7 |
| EMP012 | 0|
| EMP013 | 4.8 |
| EMP014 | 7.5 |
+--------+-----------+-------------+--------+--------------------+
mysql> select
employee_personal.firstname,employee_performance.Performance
_rating
-> where employee_performance.Performance_rating=0;
+-----------+--------------------+
| firstname | Performance_rating |
+-----------+--------------------+
| Ram | 0|
| Raghav | 0|
+-----------+--------------------+
mysql> select COUNT(employee_performance.Performance_rating)

-> FROM employee_performance
-> where Performance_rating=0;
+------------------------------------------------+
| COUNT(employee_performance.Performance_rating) |
+------------------------------------------------+
| 2|
+------------------------------------------------+
mysql> select department,
-> COUNT(employee_personal.emp_id)
-> GROUP BY department;
+-------------+---------------------------------+
| department | COUNT(employee_personal.emp_id) |
+-------------+---------------------------------+
| 3|
| 2|
| 2|
| 3|
| 2|
| 2|
+-------------+---------------------------------+
mysql> SELECT
-> department,
-> COUNT(employee_personal.emp_id)
-> FROM
-> employee_personal
-> GROUP BY
-> department;
+-------------+---------------------------------+
| department | COUNT(employee_personal.emp_id) |
+-------------+---------------------------------+
| 3|
| 2|
| 2|
| 3|
| 2|
| 2|
+-------------+---------------------------------+

+--------+--------------------+
+--------+--------------------+
| EMP001 | 8.5 |
| EMP002 | 9.1 |
| EMP003 | 8.6 |
| EMP004 | 6.2 |
| EMP005 | 0|
| EMP006 | 7.1 |
| EMP007 | 8.3 |
| EMP008 | 5.6 |
| EMP009 | 6.5 |
| EMP010 | 10 |
| EMP011 | 2.7 |
| EMP012 | 0|
| EMP013 | 4.8 |
| EMP014 | 7.5 |
+--------+--------------------+
mysql> select
emp_id,COUNT(employee_performance.Performance_rating)
-> FROM employee_performance
-> where Performance_rating=8.5;
+--------+------------------------------------------------+
| emp_id | COUNT(employee_performance.Performance_rating) |
+--------+------------------------------------------------+
| EMP001 | 1|
+--------+------------------------------------------------+
mysql> select
employee_personal.department,AVG(employee_performance.Perfor
mance_rating) AS avg_performance
-> GROUP BY employee_personal.department;
+-------------+--------------------+
+-------------+--------------------+
| 8.733333587646484 |
| 3.0999999046325684 |
| 7.700000047683716 |
| 7.3666666348775225 |
| 1.350000023841858 |
| 6.150000095367432 |
+-------------+--------------------+
mysql> select
employee_personal.department,AVG(employee_performance.Perfor
mance_rating) AS avg_performance
-> GROUP BY employee_personal.department
-> ORDER BY avg_performance DESC
-> lIMIT 1;
+------------+-------------------+
+------------+-------------------+
| 8.733333587646484 |
+------------+-------------------+
mysql> create table book_details(book_id int,title

varchar(25),primary key(book_id));
mysql> load data infile 'C:\\wamp64\\tmp\\book_details.csv'

-> into table book_details
-> ignore 1 rows;
mysql> select * from book_details;

+---------+----------------+
| book_id | title |
+---------+----------------+
| 211 | harry potter
| 212 | atomic habits
|213 | deep work
| 214 | ergonomics
|
| | Pyhton
| 217 | data science
| 218 | hunger games
| 219 | maze runner
| 220 | principles
| 221 | mockingbird
| 222 | chronicles
+---------+----------------+
mysql> select title from book_details;

+----------------+
| title |
+----------------+
|arry potter
|atomic habits
| work
|onomics
|
|
|ata science
|unger games
|ze runner
|nciples
|ckingbird
|onicles
+----------------+
mysql> ALTER TABLE book_details

-> MODIFY title text ;

+----------------+
| title |
+----------------+
|arry potter
|atomic habits
| work
|onomics
|
|
|ata science
|unger games
|ze runner
|nciples
|ckingbird
|onicles
+----------------+
mysql> ALTER TABLE book_details

-> MODIFY title varchar(50) ;

+----------------+
| title |
+----------------+
|arry potter
|atomic habits
| work
|onomics
|
|
|ata science
|unger games
|ze runner
|nciples
|ckingbird
|onicles
+----------------+
mysql> select * from book_details;

+---------+----------------+
| book_id | title |
+---------+----------------+
| 211 | harry potter
| 212 | atomic habits
|213 | deep work
| 214 | ergonomics
|
| | Pyhton
| 217 | data science
| 218 | hunger games
| 219 | maze runner
| 220 | principles
| 221 | mockingbird
| 222 | chronicles
+---------+----------------+
mysql> create table library_transaction(t_id int,u_id
varchar(4),address varchar(10),book_id int,borrowed int,fine
float,foreign key(book_id) references book_details(book_id));
mysql> load data infile 'C:\\wamp64\\tmp\\library_transaction.csv'

-> into table library_transaction
-> ignore 1 rows;
mysql> select * from library_transaction;

+------+------+------------+---------+----------+---------+
| t_id | u_id | address | book_id | borrowed | fine |
+------+------+------------+---------+----------+---------+
| 31 | A001 | dadar | 211 | 20 | 200.2 |
| 32 | B002 | bandra | 212 | 30 | 0|
| 33 | C003 | bandra | 213 | 40 | 0|
| 34 | D004 | bandra | 214 | 15 | 0|
| 35 | E005 | bandra | 215 | 60 | 0|
| 36 | F006 | juhu | 216 | 15 | 20.25 |
| 37 | J007 | andheri | 217 | 12 | 0|
| 38 | H008 | malabar hi | 218 | 11 | 0|
| 39 | I006 | goregoan | 219 | 10 | 0|
| 40 | K998 | juhu | 220 | 9 | 500.01 |
| 41 | Z009 | prabhadevi | 221 | 18 | 0|
| 42 | W223 | worli | 222 | 28 | 1000.05 |
+------+------+------------+---------+----------+---------+
mysql> desc book_details;

+---------+-------------+------+-----+---------+-------+
+---------+-------------+------+-----+---------+-------+
| book_id | int | NO | PRI | NULL | |
| title | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
mysql> select
-> book_details.book_id,
-> book_details.title,
-> library_transaction.t_id,
-> library_transaction.u_id,
-> library_transaction.address,
-> library_transaction.book_id,
-> library_transaction.borrowed,
-> library_transaction.fine
-> FROM book_details
-> JOIN library_transaction ON
book_details.book_id=library_transaction.book_id;
+---------+----------------+------+------+------------+---------+----------+---------+
| book_id | title | t_id | u_id | address | book_id | borrowed
| fine |
+---------+----------------+------+------+------------+---------+----------+---------+
| 31 | A001 | dadar | 211 | 20 | 200.2 |
| 32 | B002 | bandra | 212 | 30 | 0|
| 33 | C003 | bandra | 213 | 40 | 0|
| 34 | D004 | bandra | 214 | 15 | 0|
| 35 | E005 | bandra | 215 | 60 | 0|
| 36 | F006 | juhu | 216 | 15 | 20.25 |
| 37 | J007 | andheri | 217 | 12 | 0|
| 38 | H008 | malabar hi | 218 | 11 | 0|
| 39 | I006 | goregoan | 219 | 10 | 0|
| 40 | K998 | juhu | 220 | 9 | 500.01 |
| 41 | Z009 | prabhadevi | 221 | 18 | 0|
| 42 | W223 | worli | 222 | 28 | 1000.05 |
+---------+----------------+------+------+------------+---------+----------+---------+
mysql> select u_id,COUNT(library_transaction.borrowed)

-> FROM library_transaction;
+------+-------------------------------------+
| u_id | COUNT(library_transaction.borrowed) |
+------+-------------------------------------+
| A001 | 12 |
+------+-------------------------------------+
mysql> select * u_id,COUNT(library_transaction.borrowed)

right syntax to use near 'u_id,COUNT(library_transaction.borrowed)
FROM library_transaction' at line 1
mysql> select u_id,COUNT(library_transaction.borrowed)
-> FROM library_transaction
-> GROUP BY u_id;
+------+-------------------------------------+
| u_id | COUNT(library_transaction.borrowed) |
+------+-------------------------------------+
| A001 | 1|
| B002 | 1|
| C003 | 1|
| D004 | 1|
| E005 | 1|
| F006 | 1|
| J007 | 1|
| H008 | 1|
| I006 | 1|
| K998 | 1|
| Z009 | 1|
| W223 | 1|
+------+-------------------------------------+
mysql> select u_id,AVG(library_transaction.fine) AS avg_fine

-> GROUP BY u_id;
+------+--------------------+
| u_id | avg_fine |
+------+--------------------+
| A001 | 200.1999969482422 |
| B002 | 0|
| C003 | 0|
| D004 | 0|
| E005 | 0|
| F006 | 20.25 |
| J007 | 0|
| H008 | 0|
| I006 | 0|
| K998 | 500.010009765625 |
| Z009 | 0|
| W223 | 1000.0499877929688 |
+------+--------------------+
mysql> select u_id,MAX(library_transaction.fine)

+------+-------------------------------+
| u_id | MAX(library_transaction.fine) |
+------+-------------------------------+
| A001 | 1000.05 |
+------+-------------------------------+

-> -> into table library_transaction
-> -> fields terminated by ','
-> ;
right syntax to use near '-> into table library_transaction
-> fields terminated by ','' at line 2
-> into table library_transaction
-> ignore 1 rows;
mysql> select* from library_transaction;

+------+------+------------+---------+----------+---------+
+------+------+------------+---------+----------+---------+
| 31 | A001 | dadar | 211 | 20 | 200.2 |
| 32 | B002 | bandra | 212 | 30 | 0|
| 33 | C003 | bandra | 213 | 40 | 0|
| 34 | D004 | bandra | 214 | 15 | 0|
| 35 | E005 | bandra | 215 | 60 | 0|
| 36 | F006 | juhu | 216 | 15 | 20.25 |
| 37 | J007 | andheri | 217 | 12 | 0|
| 38 | H008 | malabar hi | 218 | 11 | 0|
| 39 | I006 | goregoan | 219 | 10 | 0|
| 40 | K998 | juhu | 220 | 9 | 500.01 |
| 41 | Z009 | prabhadevi | 221 | 18 | 0|
| 42 | W223 | worli | 222 | 28 | 1000.05 |
| 31 | A001 | dadar | 211 | 20 | 200.2 |
| 32 | B002 | bandra | 212 | 30 | 0|
| 33 | C003 | bandra | 213 | 40 | 500.5 |
| 34 | D004 | bandra | 214 | 15 | 0|
| 35 | E005 | bandra | 215 | 60 | 0|
| 36 | F006 | juhu | 216 | 15 | 20.25 |
| 37 | J007 | andheri | 217 | 12 | 2000.8 |
| 38 | A001 | dadar | 218 | 11 | 200 |
| 39 | A001 | dadar | 219 | 10 | 0|
| 40 | C003 | bandra | 220 | 9 | 500.01 |
| 41 | C003 | bandra | 221 | 18 | 1000.5 |
| 42 | C003 | bandra | 222 | 28 | 1000.05 |
+------+------+------------+---------+----------+---------+
mysql> select
-> book_details.book_id,
-> book_details.title,
-> library_transaction.t_id,
-> library_transaction.u_id,
-> library_transaction.address,
-> library_transaction.book_id,
-> library_transaction.borrowed,
-> library_transaction.fine
-> FROM book_details
-> JOIN library_transaction ON
book_details.book_id=library_transaction.book_id;
+---------+----------------+------+------+------------+---------+----------+---------+
| book_id | title | t_id | u_id | address | book_id | borrowed
| fine |
+---------+----------------+------+------+------------+---------+----------+---------+
| 31 | A001 | dadar | 211 | 20 | 200.2 |
| 31 | A001 | dadar | 211 | 20 | 200.2 |
| 32 | B002 | bandra | 212 | 30 | 0|
| 32 | B002 | bandra | 212 | 30 | 0|
| 33 | C003 | bandra | 213 | 40 | 0|
| 33 | C003 | bandra | 213 | 40 | 500.5 |
| 34 | D004 | bandra | 214 | 15 | 0|
| 34 | D004 | bandra | 214 | 15 | 0|
| 35 | E005 | bandra | 215 | 60 | 0|
| 35 | E005 | bandra | 215 | 60 | 0|
| 36 | F006 | juhu | 216 | 15 | 20.25 |
| 36 | F006 | juhu | 216 | 15 | 20.25 |
| 37 | J007 | andheri | 217 | 12 | 0|
| 37 | J007 | andheri | 217 | 12 | 2000.8 |
| 38 | H008 | malabar hi | 218 | 11 | 0|
| 38 | A001 | dadar | 218 | 11 | 200 |
| 39 | I006 | goregoan | 219 | 10 | 0|
| 39 | A001 | dadar | 219 | 10 | 0|
| 40 | K998 | juhu | 220 | 9 | 500.01 |
| 40 | C003 | bandra | 220 | 9 | 500.01 |
| 41 | Z009 | prabhadevi | 221 | 18 | 0|
| 41 | C003 | bandra | 221 | 18 | 1000.5 |
| 42 | W223 | worli | 222 | 28 | 1000.05 |
| 42 | C003 | bandra | 222 | 28 | 1000.05 |
+---------+----------------+------+------+------------+---------+----------+---------+
mysql> select u_id,COUNT(library_transaction.book_id)

-> ORDER BY u_id;
+------+------------------------------------+
| u_id | COUNT(library_transaction.book_id) |
+------+------------------------------------+
| A001 | 24 |
+------+------------------------------------+
mysql> select u_id,COUNT(library_transaction.book_id)

-> GROUP BY u_id;
+------+------------------------------------+
| u_id | COUNT(library_transaction.book_id) |
+------+------------------------------------+
| A001 | 4|
| B002 | 2|
| C003 | 5|
| D004 | 2|
| E005 | 2|
| F006 | 2|
| J007 | 2|
| H008 | 1|
| I006 | 1|
| K998 | 1|
| Z009 | 1|
| W223 | 1|
+------+------------------------------------+
mysql> select u_id,COUNT( DISTINCT library_transaction.book_id)

-> GROUP BY u_id;
+------+----------------------------------------------+
| u_id | COUNT( DISTINCT library_transaction.book_id) |
+------+----------------------------------------------+
| A001 | 3|
| B002 | 1|
| C003 | 4|
| D004 | 1|
| E005 | 1|
| F006 | 1|
| H008 | 1|
| I006 | 1|
| J007 | 1|
| K998 | 1|
| W223 | 1|
| Z009 | 1|
+------+----------------------------------------------+
mysql> select u_id,COUNT(library_transaction.book_id) AS

count_borrowed
-> where MAX(count_borrowed);
ERROR 1111 (HY000): Invalid use of group function
count_borrowed
-> where book_id=MAX(count_borrowed);
count_borrowed
-> where book_id=MAX(count_borrowed);
count_borrowed
-> where book_id=MAX(count_borrowed)
-> ;
count_borrowed
-> where MAX(count_borrowed)
-> GROUP BY u_id;
count_borrowed
-> GROUP BY u_id
-> ORDER BY book_id DESC
-> LIMIT 1;
+------+----------------+
| u_id | count_borrowed |
+------+----------------+
| W223 | 1|
+------+----------------+

count_borrowed
-> GROUP BY u_id
-> ORDER BY count_borrowed DESC
-> LIMIT 1;
+------+----------------+
+------+----------------+
| C003 | 5|
+------+----------------+
mysql> select u_id,COUNT( DISTINCT library_transaction.book_id) AS

count_borrowed
-> GROUP BY u_id
-> ORDER BY count_borrowed DESC
-> LIMIT 1;
+------+----------------+
+------+----------------+
| C003 | 4|
+------+----------------+
mysql> select u_id,book_id

-> from library_transaction
-> ;
+------+---------+
| u_id | book_id |
+------+---------+
| A001 | 211 |
| B002 | 212 |
| C003 | 213 |
| D004 | 214 |
| E005 | 215 |
| F006 | 216 |
| J007 | 217 |
| H008 | 218 |
| I006 | 219 |
| K998 | 220 |
| Z009 | 221 |
| W223 | 222 |
| A001 | 211 |
| B002 | 212 |
| C003 | 213 |
| D004 | 214 |
| E005 | 215 |
| F006 | 216 |
| J007 | 217 |
| A001 | 218 |
| A001 | 219 |
| C003 | 220 |
| C003 | 221 |
| C003 | 222 |
+------+---------+
mysql> select COUNT(DISTINCT book_id) AS count_book_id

-> where count_book_id=4;
ERROR 1054 (42S22): Unknown column 'count_book_id' in 'where
clause'
-> ORDER BY count_book_id=4;
+---------------+
| count_book_id |
+---------------+
| 12 |
+---------------+

-> HAVING count_book_id=4;
Empty set (0.00 sec)
mysql> select book_id

-> HAVING COUNT(*)=4;
Empty set (0.00 sec)
mysql> select u_id,AVG(library_transaction.book_id) AS avg_book_id

-> GROUP BY u_id;
+------+-------------+
| u_id | avg_book_id |
+------+-------------+
| A001 | 214.7500 |
| B002 | 212.0000 |
| C003 | 217.8000 |
| D004 | 214.0000 |
| E005 | 215.0000 |
| F006 | 216.0000 |
| J007 | 217.0000 |
| H008 | 218.0000 |
| I006 | 219.0000 |
| K998 | 220.0000 |
| Z009 | 221.0000 |
| W223 | 222.0000 |
+------+-------------+
mysql> select u_id,AVG(library_transaction.fine) AS avg_book_fine

-> GROUP BY u_id;
+------+--------------------+
| u_id | avg_book_fine |
+------+--------------------+
| A001 | 150.0999984741211 |
| B002 | 0|
| C003 | 600.2119995117188 |
| D004 | 0|
| E005 | 0|
| F006 | 20.25 |
| J007 | 1000.4000244140625 |
| H008 | 0|
| I006 | 0|
| K998 | 500.010009765625 |
| Z009 | 0|
| W223 | 1000.0499877929688 |
+------+--------------------+
mysql> select u_id,AVG(library_transaction.book_id) AS avg_book_id

-> GROUP BY u_id
-> ORDER BY avg_book_id DESC
-> LIMIT 1;
+------+-------------+
| u_id | avg_book_id |
+------+-------------+
| W223 | 222.0000 |
+------+-------------+
mysql> select u_id,AVG(library_transaction.fine) AS avg_book_fine

-> GROUP BY u_id
-> ORDER BY avg_book_fine DESC
-> LIMIT 1;
+------+--------------------+
| u_id | avg_book_fine |
+------+--------------------+
| J007 | 1000.4000244140625 |
+------+--------------------+
mysql> select u_id,MAX(library_transaction.fine)

-> from library_transaction;
+------+-------------------------------+
| u_id | MAX(library_transaction.fine) |
+------+-------------------------------+
| A001 | 2000.8 |
+------+-------------------------------+

+---------+-------------+------+-----+---------+-------+
+---------+-------------+------+-----+---------+-------+
+---------+-------------+------+-----+---------+-------+
mysql> desc student_demo;

+--------------+-------------+------+-----+---------+-------+
+--------------+-------------+------+-----+---------+-------+
| Stud_id | varchar(4) | YES | MUL | NULL | |
| Student_Name | varchar(40) | YES | | NULL | |
| Semester | int | YES | | NULL | |
| Subject_1 | int | YES | | NULL | |
| total | int | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
mysql> select
-> personal_demo.Stud_id,
-> personal_demo.Sname,
-> personal_demo.Gender,
-> personal_demo.Address,
-> personal_demo.Email,
-> personal_demo.Phone,
-> student_demo.Stud_id,
-> student_demo.Student_Name,
-> student_demo.Semester,
-> student_demo.Subject_1,
-> student_demo.total
personal_demo.Stud_id=student_demo.Stud_id;
+---------+--------------------+--------+----------+-------------------------------+----
--------+---------+--------------------+----------+-----------+-----------+-----------
+-----------+-----------+-------+
Phone | Stud_id | Student_Name | Semester | Subject_1 |
Subject_2 | Subject_3 | Subject_4 | Subject_5 | total |
+---------+--------------------+--------+----------+-------------------------------+----
--------+---------+--------------------+----------+-----------+-----------+-----------
+-----------+-----------+-------+
91 | 349 |
73 | 333 |
92 | 395 |
54 | 316 |
Kankekar | 1| 71 | 78 | 55 | 93 | 54 | 351
|
Kankekar | 2| 68 | 51 | 93 | 82 | 94 | 388
|
Kankekar | 3| 66 | 96 | 54 | 55 | 63 | 334
|
Kankekar | 4| 68 | 53 | 84 | 81 | 85 | 371
|
Veldandi | 1| 59 | 90 | 90 | 95 | 95 | 429
|
Veldandi | 2| 54 | 62 | 89 | 55 | 61 | 321
|
Veldandi | 3| 98 | 98 | 60 | 98 | 69 | 423
|
Veldandi | 4| 93 | 74 | 70 | 97 | 62 | 396
|
| 1| 50 | 51 | 84 | 65 | 89 | 339 |
| 2| 71 | 53 | 58 | 95 | 72 | 349 |
| 3| 75 | 89 | 87 | 78 | 56 | 385 |
| 4| 66 | 63 | 97 | 82 | 55 | 363 |
Kale | 1| 79 | 92 | 63 | 93 | 92 | 419 |
Kale | 2| 87 | 73 | 53 | 82 | 72 | 367 |
Kale | 3| 87 | 52 | 68 | 68 | 88 | 363 |
Kale | 4| 85 | 95 | 63 | 63 | 72 | 378 |
Desai | 1| 55 | 56 | 89 | 86 | 75 | 361 |
Desai | 2| 57 | 89 | 98 | 53 | 58 | 355 |
Desai | 3| 67 | 64 | 69 | 74 | 68 | 342 |
Desai | 4| 70 | 73 | 77 | 75 | 84 | 379 |
| 1| 59 | 83 | 71 | 91 | 97 | 401 |
| 2| 58 | 91 | 53 | 81 | 77 | 360 |
| 3| 62 | 69 | 81 | 74 | 81 | 367 |
| 4| 89 | 95 | 57 | 87 | 65 | 393 |
Shetty | 1| 64 | 50 | 73 | 62 | 92 | 341 |
Shetty | 2| 68 | 74 | 70 | 83 | 57 | 352 |
Shetty | 3| 61 | 57 | 64 | 55 | 89 | 326 |
Shetty | 4| 98 | 84 | 88 | 60 | 76 | 406 |
Kumbhar | 1| 81 | 90 | 96 | 50 | 99 | 416
|
Kumbhar | 2| 68 | 69 | 67 | 85 | 86 | 375
|
Kumbhar | 3| 62 | 65 | 57 | 77 | 52 | 313
|
Kumbhar | 4| 58 | 67 | 58 | 92 | 54 | 329
|
1| 91 | 57 | 65 | 65 | 56 | 334 |
2| 65 | 65 | 69 | 85 | 95 | 379 |
3| 79 | 69 | 85 | 95 | 87 | 415 |
4| 75 | 97 | 71 | 81 | 71 | 395 |
+---------+--------------------+--------+----------+-------------------------------+----
--------+---------+--------------------+----------+-----------+-----------+-----------
+-----------+-----------+-------+
mysql> desc student_demo;

+--------------+-------------+------+-----+---------+-------+
+--------------+-------------+------+-----+---------+-------+
| Stud_id | varchar(4) | YES | MUL | NULL | |
| Student_Name | varchar(40) | YES | | NULL | |
| Semester | int | YES | | NULL | |
| total | int | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+
+---------+-------------+------+-----+---------+-------+
+---------+-------------+------+-----+---------+-------+
mysql> select
personal_demo.Gender,AVG(student_demo.Subject_1) AS
avg_subj_1
personal_demo.Stud_id=student_demo.Stud_id
-> where student_demo.Semester=2
-> GROUP BY personal_demo.Gender;
+--------+------------+
| Gender | avg_subj_1 |
+--------+------------+
| Male | 67.4000 |
| Female | 63.2000 |
+--------+------------+

+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+-------+
+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+-------+
75 | 91 | 349 |
59 | 73 | 333 |
95 | 92 | 395 |
61 | 54 | 316 |
93 | 54 | 351 |
82 | 94 | 388 |
55 | 63 | 334 |
81 | 85 | 371 |
95 | 95 | 429 |
55 | 61 | 321 |
98 | 69 | 423 |
97 | 62 | 396 |
| S004 | Aaryan Raut | 1| 50 | 51 | 84 | 65
| 89 | 339 |
| S004 | Aaryan Raut | 2| 71 | 53 | 58 | 95
| 72 | 349 |
| S004 | Aaryan Raut | 3| 75 | 89 | 87 | 78
| 56 | 385 |
| S004 | Aaryan Raut | 4| 66 | 63 | 97 | 82
| 55 | 363 |
| S005 | Mayank Kale | 1| 79 | 92 | 63 | 93
| 92 | 419 |
| S005 | Mayank Kale | 2| 87 | 73 | 53 | 82
| 72 | 367 |
| S005 | Mayank Kale | 3| 87 | 52 | 68 | 68
| 88 | 363 |
| S005 | Mayank Kale | 4| 85 | 95 | 63 | 63
| 72 | 378 |
| S006 | Manushi Desai | 1| 55 | 56 | 89 |
86 | 75 | 361 |
| S006 | Manushi Desai | 2| 57 | 89 | 98 |
53 | 58 | 355 |
| S006 | Manushi Desai | 3| 67 | 64 | 69 |
74 | 68 | 342 |
| S006 | Manushi Desai | 4| 70 | 73 | 77 |
75 | 84 | 379 |
| S007 | Ritika Singh | 1| 59 | 83 | 71 | 91 |
97 | 401 |
| S007 | Ritika Singh | 2| 58 | 91 | 53 | 81 |
77 | 360 |
| S007 | Ritika Singh | 3| 62 | 69 | 81 | 74 |
81 | 367 |
| S007 | Ritika Singh | 4| 89 | 95 | 57 | 87 |
65 | 393 |
62 | 92 | 341 |
83 | 57 | 352 |
55 | 89 | 326 |
60 | 76 | 406 |
50 | 99 | 416 |
85 | 86 | 375 |
77 | 52 | 313 |
92 | 54 | 329 |
| S010 | Ragini | 1| 91 | 57 | 65 | 65 |
56 | 334 |
| S010 | Ragini | 2| 65 | 65 | 69 | 85 |
95 | 379 |
| S010 | Ragini | 3| 79 | 69 | 85 | 95 |
87 | 415 |
| S010 | Ragini | 4| 75 | 97 | 71 | 81 |
71 | 395 |
+---------+--------------------+----------+-----------+-----------+-----------+--------
---+-----------+-------+
mysql> desc employee_performance;

+--------------------+------------+------+-----+---------+-------+
+--------------------+------------+------+-----+---------+-------+
| emp_id | varchar(6) | YES | MUL | NULL | |
| Performance_rating | float | YES | | NULL | |
+--------------------+------------+------+-----+---------+-------+
mysql> select emp_id,MAX(Performance_rating) AS

highest_performer
-> FROM employee_performance;
+--------+-------------------+
| emp_id | highest_performer |
+--------+-------------------+
| EMP001 | 10 |
+--------+-------------------+
+--------+--------------------+
+--------+--------------------+
| EMP001 | 8.5 |
| EMP002 | 9.1 |
| EMP003 | 8.6 |
| EMP004 | 6.2 |
| EMP005 | 0|
| EMP006 | 7.1 |
| EMP007 | 8.3 |
| EMP008 | 5.6 |
| EMP009 | 6.5 |
| EMP010 | 10 |
| EMP011 | 2.7 |
| EMP012 | 0|
| EMP013 | 4.8 |
| EMP014 | 7.5 |
+--------+--------------------+
mysql> desc employee_personal;

+------------+-------------+------+-----+---------+-------+
+------------+-------------+------+-----+---------+-------+
| emp_id | varchar(6) | NO | PRI | NULL | |
| firstname | varchar(25) | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
mysql> select department,COUNT(emp_id)

-> from employee_personal;
+------------+---------------+
| department | COUNT(emp_id) |
+------------+---------------+
| 14 |
+------------+---------------+
mysql> desc library_transaction;

+----------+-------------+------+-----+---------+-------+
+----------+-------------+------+-----+---------+-------+
| t_id | int | YES | | NULL | |
| u_id | varchar(4) | YES | | NULL | |
| address | varchar(10) | YES | | NULL | |
| book_id | int | YES | MUL | NULL | |
| borrowed | int | YES | | NULL | |
| fine | float | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
mysql> select u_id,AVG(fine) AS avg_fine

-> GROUP BY u_id;
+------+--------------------+
| u_id | avg_fine |
+------+--------------------+
| A001 | 150.0999984741211 |
| B002 | 0|
| C003 | 600.2119995117188 |
| D004 | 0|
| E005 | 0|
| F006 | 20.25 |
| J007 | 1000.4000244140625 |
| H008 | 0|
| I006 | 0|
| K998 | 500.010009765625 |
| Z009 | 0|
| W223 | 1000.0499877929688 |
+------+--------------------+
mysql> create table library_notransaction(t_id int,u_id

varchar(4),address varchar(10),book_id int,borrowed int,fine
int,foreign key(book_id) references book_details(book_id));

-> into table library_notransaction
-> ignore 1 rows;
mysql> select * from library_notransaction;

+------+------+---------+---------+----------+------+
+------+------+---------+---------+----------+------+
| 31 | A001 | dadar | 211 | 20 | 200 |
| 32 | B002 | bandra | 212 | 30 | 0 |
| 33 | C003 | bandra | 213 | 40 | 501 |
| 34 | D004 | bandra | 214 | 15 | 0 |
| 35 | E005 | bandra | 215 | 60 | 0 |
| 36 | F006 | juhu | 216 | 15 | 20 |
| 37 | J007 | andheri | 217 | 12 | 2001 |
| 38 | A001 | dadar | 218 | 11 | 200 |
| 39 | A001 | dadar | 219 | 10 | 0 |
| 40 | C003 | bandra | 220 | 9 | 500 |
| 41 | C003 | bandra | 221 | 18 | 1001 |
| 42 | C003 | bandra | 222 | 28 | 1000 |
+------+------+---------+---------+----------+------+
mysql> select u_id,AVG(fine) AS avg_fine

-> FROM library_notransaction
-> GROUP BY u_id;
+------+-----------+
| u_id | avg_fine |
+------+-----------+
| A001 | 133.3333 |
| B002 | 0.0000 |
| C003 | 750.5000 |
| D004 | 0.0000 |
| E005 | 0.0000 |
| F006 | 20.0000 |
| J007 | 2001.0000 |
+------+-----------+
mysql> select u_id,MAX(fine)
-> FROM library_notransaction;
+------+-----------+
| u_id | MAX(fine) |
+------+-----------+
| A001 | 2001 |
+------+-----------+
mysql> select u_id,MAX(library_notransaction.fine) AS highest_fine

-> FROM library_notransaction;
+------+--------------+
| u_id | highest_fine |
+------+--------------+
| A001 | 2001 |
+------+--------------+
mysql> select u_id,fine

-> from library_notransaction
-> ORDER BY fine DESC
-> LIMIT 1;
+------+------+
| u_id | fine |
+------+------+
| J007 | 2001 |
+------+------+
mysql> select u_id, fine

-> where fine=MAX(fine);
mysql> select u_id, fine
-> where fine=(select MAX(fine) from library_notransaction;
mysql> create table sales_transaction(p_id int,product_name
varchar(10),category varchar(20),unit_price int,primary key(p_id));
mysql> load data infile 'C:\\wamp64\\tmp\\sales_transaction.csv'

-> into table sales_transaction
-> ignore 1 rows;
mysql> select * from sales_transaction;

+------+--------------+-------------+------------+
| p_id | product_name | category | unit_price |
+------+--------------+-------------+------------+
| 1001 | tv | electronics | 20000 |
| 1002 | ac | electronics | 40000 |
| 1003 | table | furniture | 5000 |
| 1004 | mobile | electronics | 15000 |
| 1005 | fridge | electronics | 30000 |
| 1006 | chair | furniture | 4000 |
| 1007 | jwellery | accessories | 500 |
| 1008 | apple | fruit | 10 |
| 1009 | tomato | vegetable | 20 |
| 1010 | onion | vegetable | 30 |
| 1011 | ladyfinger | vegetable | 40 |
+------+--------------+-------------+------------+
mysql> create table product_details(t_id int,p_id int,quantity

int,unit_price int,foreign key(p_id)references
sales_transaction(p_id));
mysql> load data infile 'C:\\wamp64\\tmp\\product_details.csv'
-> into table product_details
-> ignore 1 rows;
mysql> select * from product_details;

+------+------+----------+------------+
| t_id | p_id | quantity | unit_price |
+------+------+----------+------------+
| 1 | 1001 | 20 | 20000 |
| 2 | 1002 | 30 | 40000 |
| 3 | 1003 | 40 | 5000 |
| 4 | 1004 | 50 | 15000 |
| 5 | 1005 | 10 | 30000 |
| 6 | 1006 | 5| 4000 |
| 7 | 1007 | 200 | 500 |
| 8 | 1008 | 85 | 10 |
| 9 | 1009 | 65 | 20 |
| 10 | 1010 | 75 | 30 |
| 11 | 1011 | 90 | 40 |
+------+------+----------+------------+
mysql> desc sales_transaction;

+--------------+-------------+------+-----+---------+-------+
+--------------+-------------+------+-----+---------+-------+
| p_id | int | NO | PRI | NULL | |
| product_name | varchar(10) | YES | | NULL | |
| category | varchar(20) | YES | | NULL | |
| unit_price | int | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
mysql> select sales_transaction.p_id,

-> ;
mysql> select
sales_transaction.p_id,sales_transaction.product_name,sales_transa
ction.category,sales_transaction.unit_price,product_details.t_id,pro
duct_details.p_id,product_details.quantity,product_details.unit_pric
e
-> FROM sales_transaction
-> JOIN product_details ON
sales_transaction.p_id=product_details.p_id;
+------+--------------+-------------+------------+------+------+----------+------------
+
| p_id | product_name | category | unit_price | t_id | p_id |
quantity | unit_price |
+------+--------------+-------------+------------+------+------+----------+------------
+
| 1001 | tv | electronics | 20000 | 1 | 1001 | 20 |
20000 |
| 1002 | ac | electronics | 40000 | 2 | 1002 | 30 |
40000 |
| 1003 | table | furniture | 5000 | 3 | 1003 | 40 |
5000 |
| 1004 | mobile | electronics | 15000 | 4 | 1004 | 50 |
15000 |
| 1005 | fridge | electronics | 30000 | 5 | 1005 | 10 |
30000 |
| 1006 | chair | furniture | 4000 | 6 | 1006 | 5|
4000 |
| 1007 | jwellery | accessories | 500 | 7 | 1007 | 200 |
500 |
| 1008 | apple | fruit | 10 | 8 | 1008 | 85 | 10 |
| 1009 | tomato | vegetable | 20 | 9 | 1009 | 65 |
20 |
| 1010 | onion | vegetable | 30 | 10 | 1010 | 75 |
30 |
| 1011 | ladyfinger | vegetable | 40 | 11 | 1011 | 90 |
40 |
+------+--------------+-------------+------------+------+------+----------+------------
+
mysql> ALTER TABLE product_details

-> ADD total int;
mysql> UPDATE product_details

-> SET total=quantity*unit_price;
Rows matched: 11 Changed: 11 Warnings: 0

+------+------+----------+------------+---------+
| t_id | p_id | quantity | unit_price | total |
+------+------+----------+------------+---------+
| 1 | 1001 | 20 | 20000 | 400000 |
| 2 | 1002 | 30 | 40000 | 1200000 |
| 3 | 1003 | 40 | 5000 | 200000 |
| 4 | 1004 | 50 | 15000 | 750000 |
| 5 | 1005 | 10 | 30000 | 300000 |
| 6 | 1006 | 5| 4000 | 20000 |
| 7 | 1007 | 200 | 500 | 100000 |
| 8 | 1008 | 85 | 10 | 850 |
| 9 | 1009 | 65 | 20 | 1300 |
| 10 | 1010 | 75 | 30 | 2250 |
| 11 | 1011 | 90 | 40 | 3600 |
+------+------+----------+------------+---------+
mysql> select p_id,MAX(total)

-> FROM product_details;
+------+------------+
| p_id | MAX(total) |
+------+------------+
| 1001 | 1200000 |
+------+------------+
mysql> select product_details.p_id,MAX(product_details.total)

+------+----------------------------+
| p_id | MAX(product_details.total) |
+------+----------------------------+
| 1001 | 1200000 |
+------+----------------------------+
mysql> select p_id,total

-> FROM product_details
-> ORDER BY total DESC
-> LIMIT 1;
+------+---------+
| p_id | total |
+------+---------+
| 1002 | 1200000 |
+------+---------+
mysql> select
sales_transaction.product_name,product_details.p_id,product_detai
ls.total
sales_transaction.p_id=product_details.p_id
-> where total=(select MAX(total) from product_details.total);
ERROR 1049 (42000): Unknown database 'product_details'
mysql> select sales_transaction.product_name,product_details.total
-> where total=(select MAX(total) from product_details.total);
ERROR 1049 (42000): Unknown database 'product_details'
mysql> select
ls.total
-> where total=(select (MAX(total) from product_details.total);
right syntax to use near 'from product_details.total)' at line 4
mysql> select
ls.total
sales_transaction.p_id=product_details.p_id;
+--------------+------+---------+
| product_name | p_id | total |
+--------------+------+---------+
| tv | 1001 | 400000 |
| ac | 1002 | 1200000 |
| table | 1003 | 200000 |
| mobile | 1004 | 750000 |
| fridge | 1005 | 300000 |
| chair | 1006 | 20000 |
| jwellery | 1007 | 100000 |
| apple | 1008 | 850 |
| tomato | 1009 | 1300 |
| onion | 1010 | 2250 |
| ladyfinger | 1011 | 3600 |
+--------------+------+---------+
mysql> select
ls.total
-> ORDER BY product_details.total DESC
-> LIMIT 1;
+--------------+------+---------+
+--------------+------+---------+
| ac | 1002 | 1200000 |
+--------------+------+---------+
mysql> select MAX(total)

-> from product_details;
+------------+
| MAX(total) |
+------------+
| 1200000 |
+------------+
mysql> JOIN product_details ON

-> ;
right syntax to use near 'JOIN product_details ON
ORDER BY pro' at line 1
mysql> select
ls.total
-> where product_details.MAX(total);
ERROR 1630 (42000): FUNCTION product_details.MAX does not exist.
Check the 'Function Name Parsing and Resolution' section in the
Reference Manual
mysql> select
ls.total
-> ;
+--------------+------+---------+
+--------------+------+---------+
| tv | 1001 | 400000 |
| ac | 1002 | 1200000 |
| table | 1003 | 200000 |
| mobile | 1004 | 750000 |
| fridge | 1005 | 300000 |
| chair | 1006 | 20000 |
| jwellery | 1007 | 100000 |
| apple | 1008 | 850 |
| tomato | 1009 | 1300 |
| onion | 1010 | 2250 |
| ladyfinger | 1011 | 3600 |
+--------------+------+---------+
mysql> select
ls.total
-> ORDER BY product_details.total DESC;
+--------------+------+---------+
+--------------+------+---------+
| ac | 1002 | 1200000 |
| mobile | 1004 | 750000 |
| tv | 1001 | 400000 |
| fridge | 1005 | 300000 |
| table | 1003 | 200000 |
| jwellery | 1007 | 100000 |
| chair | 1006 | 20000 |
| ladyfinger | 1011 | 3600 |
| onion | 1010 | 2250 |
| tomato | 1009 | 1300 |
| apple | 1008 | 850 |
+--------------+------+---------+
mysql> select
ls.total
-> LIMIT 2;
+--------------+------+---------+
+--------------+------+---------+
| ac | 1002 | 1200000 |
| mobile | 1004 | 750000 |
+--------------+------+---------+
mysql> select p_id , quantity

-> where quantity>100;
+------+----------+
| p_id | quantity |
+------+----------+
| 1007 | 200 |
+------+----------+

-> where quantity>90;
+------+----------+
| p_id | quantity |
+------+----------+
| 1007 | 200 |
+------+----------+

-> where quantity>=90;
+------+----------+
| p_id | quantity |
+------+----------+
| 1007 | 200 |
| 1011 | 90 |
+------+----------+
mysql> desc sales_transaction;

+--------------+-------------+------+-----+---------+-------+
+--------------+-------------+------+-----+---------+-------+
| p_id | int | NO | PRI | NULL | |
| product_name | varchar(10) | YES | | NULL | |
| category | varchar(20) | YES | | NULL | |
| unit_price | int | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
mysql> select category,AVG(unti_price) AS avg_unit_price

-> GROUP BY category;
ERROR 1054 (42S22): Unknown column 'unti_price' in 'field list'
mysql> select category,AVG(unit_price) AS avg_unit_price
+-------------+----------------+
| category | avg_unit_price |
+-------------+----------------+
| electronics | 26250.0000 |
| furniture | 4500.0000 |
| accessories | 500.0000 |
| fruit | 10.0000 |
| vegetable | 30.0000 |
+-------------+----------------+
mysql> select category,AVG(unti_price)

ERROR 1054 (42S22): Unknown column 'unti_price' in 'field list'
mysql> select category,AVG(unit_price)
+-------------+-----------------+
| category | AVG(unit_price) |
+-------------+-----------------+
| electronics | 26250.0000 |
| furniture | 4500.0000 |
| accessories | 500.0000 |
| fruit | 10.0000 |
| vegetable | 30.0000 |
+-------------+-----------------+
mysql> select category,AVG(unti_price)

-> ;
ERROR 1054 (42S22): Unknown column 'category' in 'field list'
mysql> select category,AVG(unit_price)
-> GROUP BY category
-> ORDER BY unit_price DESC
-> LIMIT 1;
+-------------+-----------------+
| category | AVG(unit_price) |
+-------------+-----------------+
| electronics | 26250.0000 |
+-------------+-----------------+
mysql> select AVG(total)

+-------------+
| AVG(total) |
+-------------+
| 270727.2727 |
+-------------+
mysql> select p_id

-> from product_details
-> ;
+------+
| p_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
| 1006 |
| 1007 |
| 1008 |
| 1009 |
| 1010 |
| 1011 |
+------+
mysql> select p_id ,total

-> from product_details
-> where total>270727.2727;
+------+---------+
| p_id | total |
+------+---------+
| 1001 | 400000 |
| 1002 | 1200000 |
| 1004 | 750000 |
| 1005 | 300000 |
+------+---------+

+------+------+----------+------------+---------+
| t_id | p_id | quantity | unit_price | total |
+------+------+----------+------------+---------+
| 1 | 1001 | 20 | 20000 | 400000 |
| 2 | 1002 | 30 | 40000 | 1200000 |
| 3 | 1003 | 40 | 5000 | 200000 |
| 4 | 1004 | 50 | 15000 | 750000 |
| 5 | 1005 | 10 | 30000 | 300000 |
| 6 | 1006 | 5| 4000 | 20000 |
| 7 | 1007 | 200 | 500 | 100000 |
| 8 | 1008 | 85 | 10 | 850 |
| 9 | 1009 | 65 | 20 | 1300 |
| 10 | 1010 | 75 | 30 | 2250 |
| 11 | 1011 | 90 | 40 | 3600 |
+------+------+----------+------------+---------+
mysql>

SQL Practice

Uploaded by

Copyright:

Available Formats

SQL Practice

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Practice

Uploaded by

Copyright:

Available Formats

SQL PRACTICE:

mysql> use podar3;

mysql> load data infile 'C:\\wamp64\\tmp\\personal_demo'

mysql> select * from personal_demo;

mysql> ALTER TABLE personal_demo

mysql> ALTER TABLE personal_demo

mysql> select * from personal_demo;

mysql> create database podar3;

mysql> use podar3;

mysql> load data infile 'C:\\wamp64\\tmp\\personal_demo.csv'

mysql> select * from personal_demo;

mysql> create table student_demo(Stud_id

mysql> load data infile 'C:\\wamp64\\tmp\\student_demo.csv'

mysql> select * from student_demo;

mysql> desc personal_demo;

mysql> UPDATE student_demo

mysql> select * from student_demo;

mysql> select Student_Name,Semester

mysql> select Student_Name,Semester ,total

mysql> select AVG(Semester) AS avg_semester

mysql> select Semester, AVG(Semester) AS avg_semester

mysql> desc personal_demo;

mysql> select personal_demo.Phone,Student_demo.Student_Name

mysql> select * from Student_demo;

mysql> select Student_Name,total from Student_demo where

mysql> select Student_Name,Semester,total

mysql> select Student_Name,Semester,

mysql> select Student_Name,

mysql> select Student_Name,

mysql> select Semester,

mysql> select Semester,

mysql> load data infile 'C:\\wamp64\\tmp\\employee_personal.csv'

mysql> select * from employee_personal;

mysql> select * emp_id from employee_personal;

mysql> create table employee_performance(emp_id

mysql> load data infile

mysql> select* from employee_performance;

mysql> desc employee_personal;

mysql> select employee_personal.department,

mysql> select employee_personal.firstname,

mysql> select * from employee_performance;

mysql> select * from employee_personal;

mysql> select COUNT(employee_performance.Performance_rating)

mysql> select * from employee_performance;

mysql> create table book_details(book_id int,title

mysql> load data infile 'C:\\wamp64\\tmp\\book_details.csv'

mysql> select * from book_details;

mysql> select title from book_details;

mysql> ALTER TABLE book_details

mysql> select title from book_details;

mysql> ALTER TABLE book_details

mysql> select title from book_details;

mysql> select * from book_details;

mysql> load data infile 'C:\\wamp64\\tmp\\library_transaction.csv'

mysql> select * from library_transaction;

mysql> desc book_details;

mysql> select u_id,COUNT(library_transaction.borrowed)

mysql> select * u_id,COUNT(library_transaction.borrowed)