Reference no: EM133271864

Question 1. INSERT INTO directory (LastName) VALUES
('Batra'),('Sehgal'),('JBhatia'),('Sharma'),('Mehta')

Question 2. SELECT LastName FROM Directory WHERE LastName like ‘_a%'
a. SELECT lastname FROM Directory WHERE lastname not like ‘%a'

2. CREATE TABLE Teacher(ID INT,Name VARCHAR(20),Department
VARCHAR(20),HireDate VARCHAR(20),Category VARCHAR(20),Gender VARCHAR(20),Salary VARCHAR(20))

INSERT INTO teacher(ID,Name,Department,HireDate,Category,Gender,Salary) VALUES
('1','Ana Smith','SocialStudies','1994-03-17','TGT','F','25000'),
('2','David Adams','Art','1990-02-12','PRT','M','20000'),
('3','Catherine King','English','1980-0516','PGT','F','30000'),
('4','James Jacob','English','1989-10-16','TGT','M','25000'),
('5','Jasper Powell','Hindi','1990-08-01','PRT','F','22000'),
('6','Daisy Collens','Math','1980-03-17','PGT','F','21000'),
('7','Sam Andrew','Science','1994-09-02','TGT','M','27000'),
('8','Sonia','Math','1980-11-17','TGT','F','24500')

A. SELECT * FROM Teacher WHERE Category = 'PGT'
a. SELECT Name FROM teacher WHERE (Department = 'Hindi') AND (Gender = 'F')
b. SELECT Name, Department, Hiredate FROM teacher ORDER BY HireDate
c. SELECT DISTINCT(Category) FROM teacher

Question 3. CREATE TABLE ITEMS ( ITEM_NO VARCHAR(22), COST VARCHAR(22) );
INSERT INTO `items` (`ITEM_NO`, `COST`) VALUES ('101', '5000'), ('102', 'NULL'), ('103', '4000'), ('104', '6000'), ('105', 'NULL');

a. SELECT COST +100 FROM ITEMS WHERE ITEM_NO > 103

4. CREATE TABLE Members( Mname VARCHAR(6) );

Question 4. Select Mname from members where Mname like '%v'
Select Mname from members where Mname like "%e%";

Question 5. Michelle, a student of class XI, created a table "RESULT". Grade is one of the column of this table. To find the details of students whose Grades have not been entered, he wrote the following MySQL query, which did not give the desired result. SELECT * FROM Result WHERE Grade= "Null";
Help Michelle to run the query by removing the errors from the query and write thecorrect Query.

Question 6. Mr. Adams is using a table with following columns: Name, Class , Course_Id, Course_name He needs to display names of students, who have not been assigned any stream or have been assigned Course_name that ends with "economics". He wrote the following command, which did not give the desired result. SELECT Name, Class FROM Students WHERE Course name = NULL OR Course name="%economics"; Help Mr. Adams to run the query by removing the error and write the correct query

Question 7. CREATE TABLE Projects (

ID INT(3) ,
ProjName VARCHAR(15) ,
ProjSize VARCHAR(15) ,
StartDate VARCHAR(15),
EndDate VARCHAR(15),
COST VARCHAR(15)
);

INSERT INTO projects( ID, ProjName, ProjSize, StartDate, EndDate, Cost ) VALUES
( 1, 'Payroll-MMS', 'Medium', '2006-03-17', '2006-09-16', 60000 ) ,
( 2, 'Payroll-ITC', 'Large', '2006-02-12', '2008-01-11', 500000 ) ,
( 3, 'IDMgmt-LITL', 'Large', '2008-06-13', '2009-05-21', 300000 ) ,
( 4, 'Recruit-LITL', 'Meduim', '2008-03-18', '2008-06-01', 50000 ) ,
( 5, 'IDMgmt-MTC', 'Small', '2007-01-15', '2007-01-29', 20000 ) ,
( 6, 'Recruit-ITC', 'Medium', '2007-03-01', '2007-06-28', 50000 )

a. To display all information about projects of"Medium" ProjSize
b. To list the ProjSize of projects whose ProjName ends with LITL.
c. To list ID, Name, Size, and Cost of all the projects in descending order of StartDate.

Question 8. CREATE TABLE Garment (

GCODE VARCHAR(15) ,
GNAME VARCHAR(15) ,
SIZE VARCHAR(15),
COLOUR VARCHAR(15),
PRICE VARCHAR(15)
);

INSERT INTO garment(GCODE, GNAME, SIZE, COLOUR, PRICE) VALUES

('111','TShirt', 'XL', 'red', '1400.00'),
('112','Jeans', 'L', 'Blue', '1600.00'),
('113','Skirt', 'M', 'Black', '1100.00'),
('114','Ladies Jacket', 'XL', 'Blue', '4000.00'),

a. To display names of those garments that are available in ‘XL' size.
b. SELECT GCODE, GNAME FROM garment WHERE GNAME LIKE 'Ladies%'
c. SELECT GCODE, GNAME FROM garment WHERE PRICE BETWEEN 1000 AND 1500
d. SELECT GNAME FROM garment WHERE SIZE IN ('M','L') AND PRICE > 1500

Question 9. In MySQL, Sumit and Fauzia are getting the following outputs of ItemCodes for SELECT statements used by them on a table named ITEM.(Both have used the SELECT statements on the same table ITEM). Which extra keyword has Fauzia used with SELECT statement to get the above output?

Question 10. Write the statement using ‘OR' logical operator : SELECT first_name, last_name, subject FROM studentdetails WHERE subject IN (‘Maths', ‘Science');