QUERY 1 Write a SQL query to create a database. [vorston 19.0.10045. 3805] Ce) Microsoft Corporation: Ali rights reserved. c:\users\pcomysal_-u root -p (ei reenter eee Your mys@u connection id is io Copyright (c) 2000, 2024, oracie and/or its erfiietes. type “help:* or “\h* for help. type “\c’ to clear the current input statement rows in set (0.00 sec) Query OK, 1 row affected (0.00 sec) rows in set (0.00 <0c) QUERY 2 Write a SQL query to create student table with the student id, cl: section, gender, student name and marks attributes where the student id is the primary key. rysol> create table student(student_ID int(14) prinary key ,Class int,Section char Gender char Nane varcharS@) Marks Ant); Query OC, © rous affected, 4 warning (2.81 sec) rysal> desc student; | Held | Type | Mull | Key | oefalt | extra | | student 10 | int [wo Jem jmu | | [class ~ | int [vs] mu [| [ section | char(t) ves | fm || [Gender | char(t) YES | fm || | Wane | vareharse) | YES | fm | | [Marks | int [ys] fm | | 6 rows in set (2.08 sec)QUERY 3 Write a SQL query to insert the detai student tal mysqi> insert inte student values(2345,22," Qkery ox. a row affected (eee ses) st 10 students in the Loe shelini",90)2 mysql> insert into student values(3412,12,"A","F","Siya",78)3 Query 0K, "a row erfected (0.00 see) eee eee Query OK, a row affected (0.00 sec) sp, osedai“.as): mysql> inzert into student values(a021.10,"G".“N”.~Ndtin”.08): (epoca oelecrece aiCeteeteesy eee ee geet Bkery ox. "a row affected (o.08 see) evSachin” 58): mysai> insert inte student ve1ues(7392,9, "0". Query ox: "a row affected (0-00 see) 27chena™,89)2 mysql> insert into student values(9254,12,” Query ox, a row errected ([Link] see) STP "Part”,94)5 mysql> iacont into student values(7245,11,7C7,“N™,~Mantch",78): Query OK; a row affected (0.00 sec) eater oneee Blery ox, a nou arracted (evae sos) wee taanki".23); mysql> insert inte student velues(9ee2,20,"0",-N™, “Somnath” .67): Qkery ox." a row affected (8.00 sec) Ri epamroanpo student in the apzmanano,QUERY 7 Write a SQL query to display student_Id, Name and marks of those students who are scoring marks more than 30. mysql> select * from student; | student_10 | Class | Section | Gender | Name | Marks | | zeis| 2 1c le | sonam | 30 | i 2e19 | 12 | 8 lm | karan | 45 | 1 2345 | 42 | C IF | Shaling | 90 | | 3a2 | 2A Ie | siya | 78] | 362 | 11 | C Ie |sejat | 35 | | 3564] 12 | C IF | sunita | 50 | | 3ezi| 11. | 6 [a | raghav | 27 | | 4o21| 10. | G Is | itin | 64 | | 725 | a1 tc In | Manish | 78 | | 7352] 9 | F Ie | anki | 89 | | 7392] 9/8 |" | Chand | 89 | | essa] 5 1B In | Sachin | 56 | | ssi | 10 | 8 Im | Sommath | 67 | 13 rows in set (0.00 sec) mysql> select student_id,name,marks from student where marks>30; | student_id | name | marks | | 2019 | karan | 45 | | 2345 | Shalini | 90 | | 3212 | siya | 78 | | 3462 | Sejal | 85 | | 3564 | sunita | 58 | | 4621 | Nitin | 64 | | 7245 | Manish | 78 | | 7352 | Janki | 89 | | 7392 | Chand | 89 | | 8954 | Sachin | 56 | | 9801 | Somnath | 67 | 11 rows in set (0.00 sec) mysql> QUERY & Write a SQL query to find the average marks from the student table. -+ 1 row in set (@.00 sec) mysql>QUERY 9 Write a SQL query to find the number of students who are from section “a, mysql> select Name from student where section="A"; —_ 1 row in set (8.08 sec) QUERY 10 Write a SQL query to add a new column email in the student table with appropriate data type. mysql> desc student; ee “+ + | Field | Type | mult | Key | Default | extra | + ate + | student_rD | int [no | pRr| nu | I | Class | int | ves | Jw | | | Section | char(1) | Yes | | NULL I | | Gender | char(1) | ves | | NULL I | | Name | varchar(se) | YES | Jw | | | Marks | int yes | [wu | I + + + 6 rows in set (0.00 sec) mysql> alter table student add Email varchar (5) not null; Query OK, @ rows affected (0.01 sec) Records: @ Duplicates: @ Warnings: @ mysql> desc student; + + | | Type | mult | Key | Default | extra | + weteceee et | student_iD | int [no | PRI | Nu | | | Class | int | ves | | NULL I | | Section | char(1) lves | Jw | | | Gender | char(1) | ves | Jw | | | Name | varchar(se) | YES | Tnu | I | Marks | int | ves | [wu | I | Email | varchar(se) | NO | [me | I + + 7 rows in set (0.08 sec) mysql>QUERY 18 Shiva is using a table with the following detail: Students(Name, Class, Stream_Id, Stream_name) Write a SQL QUERY to display the name of students who have not been assigned any stream or assigned stream_name that end with “Computers”. mysql> select*from students; + + Ss + | Name | Class | Stream_Id | Stream Name I + | Siya | 12] 7 | Science | | Sahin | 12 | 677 | conmerce I | shalini | 11 | 48 | arts with computer | | sejal | 10 | 919 | arts with eco | | Navin | 12] 981 | arts with computer | | anjali | 11 | 123 | science with computer | | Manish | 11 | 112 | Science I | ritik | 6] 432 | NULL | + + 8 rows in set (0.00 sec) mysql> select Name from students where stream_name like “Xcomputer”; 3 + | Name | shalini | Navin | anjali | + rows in set (0.08 sec) | I I = 3 mysql> select Name from students where stream_name is null; teseecoct | Name | + | ritik | + + 1 row in set (8.0 sec) mysql>Write a SQL query to add email id's each student in the previously created email column. mysql> update student sot Email-“conma@s241" where Name: Query OK, 1 row affected (6.60 sec) Rows matched: 1 Changed: 2. Warnings: © mysql> update student set Email="karan276124" where Name="karan”; Query OK. 1 row affected (8.00 sec) Rows matched: 1 Changed: 1. Warnings: © mysql> update student set tmoil-"sholinishrivas@9252" where Name-"Shalini™; Query OK, 1 row affected (6.00 sec) Rows matched: 1 Changed: 1. Warnings: © mysql> update student set Email="siya63s21" Query OK.'1 row affected (0.00 sec) Rows matched: 1 Changed: 1. Warnings: © mysql> update student set emaii="sejal~2452" where Name = ~SeJoi Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1. Warnings: © mysql> update student set fmail-"sunitagee7” where Name = “sunit Query OK, 1 row affected (0-00 roc) Rows matched: 1 Changed: 1. Warnings: © mysql> update stucent set fmail-"ragnaveeo3” where Name = ~Ragha\ Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1. Warnings: © mysql> update student set Email="niting123" where Name = "Nitin"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1. Warnings: © mysql> update student set Email. Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 2” Warnings: 0 manish35272" where Name = “Manish”; mysql> update student set Email-"Janki@a73452" where Name - “Janki"; Query OK, i row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: @ mysql> update student set Email-“chandbabug2421" where Name - “Chand”; Query OK, 1 row affected (0.00 sec) mysql> update student set Email-“saching@79S" where Name ~ “Sachin; Query Ok) a row affected (0.00 sec) Rows matched: 1 Changed: 1 warnings: @ mysql> update student set emali="somatngsea7s” wnere Name = “Somatn™; Query Ok, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: © mysql> select * from student; | student_r0 | Class | section | Gender | Name 1 wae] aa] e Te | zone | 38 | 1 ! 219 | 12 |B [mM [karan | 45 | karanz76124 1 ! 25 | 421 C le | Shalini | 99 | shalinishrivasgo3s2 | 1 Sez] az ta Ie | Stye | 7a | atyeoasaa 1 1 poz | aac IF | Sejal | 85 | sejatzas2 | ! 3564 | 12 | C le | sunita | 59 | sunitagee7 1 1 jen | 2216 [mM | raghay | 27 | raghaveos3 1 { 4521 | 10 | G [M [Nitin | 68 | aleingiza 1 | yas | 111 c | M [Manish | 78 | manisnaso72 | 1 7352 | 9 | F IF | Sank | 89 | jankige73ase ! ' 72 | 9 |B [| Chard | 99 | chandbabugsaza | 1 goa | os |B [4 | sachin | 55 | sachingazas 1 I soi | 16 | 8 1M | Somath | 67 | somatigse473 I 35 rus in cot (@100 zee)QUERY 20 Write the sort notes on Group by, having and where clause with example. select age,count(id) as students counts from info group by age: | Students counts | Filters grouped data. mysql> select count (id) as no_of_ students, age from info group by age having age=1 | no_of_students | age | 1 1 row in set (@.0@ sec) mysql> Where: Filters rows before grouping. mysql> select * from student where marks >30; | student_ID | Class | Section | Gender | Name | Marks | Email | + | 29 | 12 | H Im | karan | 47 | karan276124 | | 235 | 12 | C | | Shalini | 95 | shaliinishrivas@9352 | | 32 | 12 | A |e | Siya | 82 | siya63521 | | 34e2 | | P [F | Sejal | 99 | sejal*zasz | | 3564 | 12 | E [& | sunita | 53 | sunitagee7 | | 4621 | 10 |G [mM | Nitin | 67 | nitingi23 | | 7245 | 11 | R |" | Manish | 82 | manish35272 | | 7352| 9 | F |& | Janki | 92 | jankiga734s2 | | B2| 910 [mM | Chand | 92 | chandbabugs421 | | a954| 5 | 9 [m | Sachin | 59 | sachings795 | | ssl] 10 | 8 Im | Somnath | 70 | sonnath@s6473 | 11 rows in set (8.00 sec)QUERY 27 How many characters are there in string “ CANDIDE ”. th(“CANDIDE”) as Character_count; Character_count | row in set (@.0@ sec) QUERY 28 Create a customer(customer_id, name, Country)table and find the number of customer from each country in the table using group by. aysql> create table custoner(custower id int,nane vanchar(S8), Country vanchar(9)); uery OK, @ rows affected (0.00 sec) iysal> select country count(*) a5 Custoner Count from custoner group by country, reseeeeefennecenecennnnat | country | Customer Cont | + mat | India | 2] | Briten | 1] |r | 1] Juss | a] receescenfece + 4 rows in set (0.08 sec) QUERY 29 Consider the following SQL string “Active Number” and write the command to display: (A) “Act” (B) “Numb”.QUERY 23 Write the SQL QUERY to increase 20% salary of the employee whose nce more than 5 year of the table Emp(id, name, salary, experience). mysql> select*from Emp; wr | id | name | salary | experience | 9 rows in set (0.00 sec) mysql> update Emp set salary = salary*®.2 where experiencers; Query OK, 3 rows affected (8.00 sec) Rows matched: 3 Changed: 3 Warnings: @ mysql> select*from Emp; a oe eee nenar | salary | experience 9 rows in set (0.00 sec)QUERY 24 Display 4 characters extracted from 5 right character onwards from string “ ABCDEFG ”. mysql> select substring("ABCDEFG",5,4)as extactedstring; not | extactedstring | 1 row in set (@.0@ sec) mysql> QUERY 25 Write a Query to create a string from the ASCII values 65,67.3,68.3. mysql> select char(65)+char(67)+char(68)as resultstring; — | resul 1 row in set, 3 warnings (8.00 sec) QUERY 26 Display names “MR. MODI” and “MR. SHARMA? into lowercase mysql> select lower("[Link]")as Modi, lower("[Link]")as Sharma; + steseeesene ee + | Modi | Sharma =| a | [Link] | [Link] | + “+ 1 row in set (0.00 sec)QUERY 19 Write the difference between drop, delete command with example. Drop: Remove the entire table. mysql> show tables; = | Tables_in_shalini mysql> drop table teacher; Query OK, @ rows affected (0.01 sec) mysql> show tables: | + Tables in_shalini | class i | Tables in i | employee | | class i | student |= | | students 1 I students I | teacher | a + a + 5 rows in set (0.00 sec) Delete: Removes all rows but keeps the table structure. mysql> select ~ from teacher: CLASS | SECTION | GENDER tonaatot ° 8 ® 6 | ea ae mysql> select + from teacher: Empty set (@.00 sec) mysql> desc teacher; Fiela | Type Nuala, i i i i i [sro | int ives | i 1 | ScNAME | varchar(se) | Yes | 1 1 | class | dat Ives | ! ! | Sectron | charc2) Ives | 1 ! 1 | GENDER | charca) Ives | 1 1 1 [marks | ine Ives | 1 ! 1 I Doe’ | date Ives | 1 ! 1QUERY 21 Write the SQL QUERY to find out the square root of 26. mysql> select SORT(26) as squarercot: 1 1 | 5.0990195135927845 | a row in set (@.e0 sec) mysql> — QUERY 22 Shiva is using a table employee. It has following details ; Employee(Code, Name, Salary, DeptCode). Write the SQL QUERY to display maximum salary department wise. mysql> select*from employee; | Code | Name | salary | Deptcode rous in set (0.0 sec) mysql> select DeptCode,max(salary) as maxsalary from employee group by DeptCode; | deptcode | maxsalary | | 110 | 10000 | I 7| 12000 | | 675 | 9800 | 17} 100 | | 321. | 30080 | | 765 | 40000 | 1 sa | 20060 | 1 23 | 6000 | 8 rows in set (0.00 sec) mysql>QUERY 16 Write a SQL query to display the unique section name from the student table. mysql> select distinct(Section) from student; = [> | [H ! Ic | [a | le | [I ! [R ! [8 | es I 9 rows in set (8.00 sec) mysql> QUERY 17 Shiva student of class XII created a table “CLASS”. Grade is one of the columns of this table. Write the SQL QUERY to find the details of students whose grade have not been entered. mysuee mysql> select= from CLASS; gender | FE m m = m F FE 1 1 1 1 I 1 + rows in set (@.e@ sec) mysql> select™ from CLASS where Grade is null; gender | Grade 1 | chand | 890 | M | nue | sejal_ | sea | F [nwuce | | sachin | 234 | M | nutes | nia | 546] F Ionute 4 rows in set (@.0@ sec)QUERY 12 Write a SQL query to display the information of name starts with “S”. | the students, whose nysql> select * from student where Nane like *S%'; He + + | student_ID | Class | Section | Gender | Name | Marks | Email | + + | 21g | 12] 0 JF | sonam | 30 | sonma@s241 | | 2345 | 12 | C [F | Shalini | 90 | shalinishrivas@e3s2 | | 3412 | 12] A |F | siya | 78 | siya63s21 | | 3462 | 11 | P |F | Sejal | 85 | sejal*2452 | | 3564] 12 | € [Fr | sunita | 50 | sunitagee7 | | soa | 5 | 9 |" | Sachin | 56 | sachingg795 | | oe01| 10 | 8 [4 | Somnath | 67 | somnath@s6473 | be + 7 rows in set (8.00 sec) rysql> QUERY 13 Write a SQL query to display the student id, Name, marks, email of male students in ascending order of their nami ‘order by Name ‘Nane,Marks,enail from student where gender- + atee “+ | student_id | Name | Marks | email | 7392 | Chand 89 | chandbabug3421 2e19 | karan 45 | karan276124 7245 | Manish 78 | manish35272 | | | | ! | | 4621 | Nitin | 64 | niting123 | I | part | raghav 27 | raghavee93 54 | Sachin 56 | sachings795 a | Somnath 67 | somnath@s6473 7 rows in set (0.00 sec) aysql>QUERY 14 Write a SQL query to display the student _id, gender, name, marks, email, of male students in descending order of their marks. aysql> select student_id , gender, Nane,Marks,enail from student order by Marks DESC; + = oa + | student_id | gender | Name | Marks | email. | in | 2345 | F | Shalini | 99 | shalinishrivas@93s2 | I 7352 | F | Janki_ | 89 | janki@a73452 | I 7392 | | Chand | 89 | chandbabugsazt_ | | 3462 | F | Sejal_| 85 | sejal*2452 | | 3412 | F |Siya | 78 | siya63s2a | ! 7245 |W | Manish | 78 | manish35272 | | 9891 | 4 | Somnath | 67 | somnath@s6473 | I aon | M [Nitin | 64 | nitingi23 | I 8954] N | Sachin | 56 | sachinga795 | I 3564 | F | sunita | 50 | sunitagoo7 | I 2ei9 | | karan | 45 | karan276124 | I 2018 | Ff | sonam | 30 | sonmags241 | I 3621|.M | raghav | 27 | raghaveoo3 | + + 13 rows in set (8.00 sec) mysql> mysql> dese student; 1 { student_ro | int i No | Class int | ves | Section char(a> I ves | Gender char(a), | ves | Name Varchar(se) | ves ! int | ves I han(se) | NO 7 rows in set (0.03 sec) mysql> alter table student => drop INDEX Section; Query Ok, @ rows affected (8.02 sec) Records: @ Duplicates: @ Warnings: @ mysql> desc student; | Field | Type | Null | Key | Default | Extra | | student_rD | int [No | PRE | NULL 1 | Class | int Ives | [nue 1 | Section | char(1) Ives | [nue 1 | Gender | char(1) yes | [nue 1 | Name | varchar(se) | Yes | [nue | | Marks | int lyes | [nue | ! | email | varchar(se) | NO | fue I 7 rows in set (0.00 sec)QUERY 5 Write a SQL query to increase the marks by 5% for those students who are scoring marks more than 30. mysql> select * from students | emaiz | student iD | Class | Section | Gender | Name | Marks I i 2018 | io ie {Sonam | 30 | sorme@szaa I | 209 | In Tm | karan | 45 | karanz7eiz0 | | 2345 | Ic IF | Shalint | 90 | shalinishrivasgo3s2 | | 3422 | a IF I siya” | 78 | siyasssaa | | 3ae2 | le IF | sejar | 85 | sejal*zasz | | 3564 | le IF I sunita | 50 | sunitagea7 | | 3622 | it Tm | raghay | 27 | raghevoos3 | | 4521 | Ie [mn [nitin§ | 98 | nitingnz3. | | 7245 | Ik Tm | Manish | 78 | “manish35272 | | 7382 | Ir IF | Sanki | Ge | janieigaraas2 | I 7392 | lo Im | Chand | 88 | Chandbabugs21 | | 8954 | 13 1m | Sachin | 56 | sachings795 | I seo1 | le Tm | Somath | 67 | someth@sea73 I 13 rows in set (0.00 sec) mysql> update student set Marks~marks +(marks*@.05) where marks 230: Query Ox, 11 rows affected (0.00 sec) Rows matched: 11. Changed: 11° Warnings: © mysql> select * from student; | student_10 | Class | Section | Gender | Name | Marks | Email I i zeis | 12 | © it i Sonam | 30 | sonmegszaa i | pers | a2 | H Im [karan | 47 | karara76124 | I 2345] az ic Ir | shaling | 95 | shalinishrivasge3s2 | | Baz] a2 | A IF I siya” | 82 | siyasssaa | | 3ae2 | a2 | P IF | Seja1_ | 89 | sejale2a52 | | 356 | 2 LE Ir | sunita | 53 | sunitagoo7 I | 3ear| a2 |r Im | raghav | 27 | raghavoos3 | | 4521 | 10 1G In [nisin | 67 | nitingnoa | | 724s | 41 | R 1m [Manish | 82 |“ manishasa72 | | 7352] 9 | F IF | danki | 92 | jankiga73a52 | ! 73821 3910 Im | Chand | 92 | chanébabugsaz1 | | sos4 | 5 19 Im | Sachin | 59 | sachings795 | I seer | 10 | 8 Tm | Somath | 70 | someth@se473 I QUERY 6 Write a SQL query to display the entire content of the table. mysql> select * from student; ] student_10 | Class | section | Gender | Name | Marks | i 2e1s Sa] Te j sonam | 30 | 2019 2218 Im Karan a5 | 234s azic Le Shalini se | 3432 a2 1A le Saye zs | 3462 wae Te Sejar as | 35ea Bowie le sunita se | 3621 3116 Im Faghav 27 1 asoa aoiG im Nitin 6a | 7245 azic Im Manish zs | 3382 ole feed anki a9 | 3392 sie Im Chana ss | 3353 Sie im Saenin Se | Seon S im Somnath 67 1