SQL query Demo

SQL query Demo

• 1.
  Department of InformationTechnology 1Data base Technologies (ITB4201) Introduction to Structured Query Language (SQL) Part 2 Dr. C.V. Suresh Babu Professor Department of IT Hindustan Institute of Science & Technology
• 2.
  Department of InformationTechnology 2Data base Technologies (ITB4201) Action Plan • SQL Introduction • Simple Queries in SQL • Quiz
• 3.
  Department of InformationTechnology 3Data base Technologies (ITB4201) Database queries • SELECT queries are used to extract information from a database: Example: SELECT first_name, last_name FROM president WHERE state = ‘NY’;
• 4.
  Department of InformationTechnology 4Data base Technologies (ITB4201) More database queries • More examples SELECT first_name, last_name FROM president WHERE birth_date like ’19%’; SELECT count(*) FROM president WHERE state = ‘VT’; SELECT first_name, last_name FROM president WHERE state IN (ME,NH,VT,MA,CT,RI);
• 5.
  Department of InformationTechnology 5Data base Technologies (ITB4201) How to create a DB table? • Use CREATE TABLE query, specifying fields and column types: Example: CREATE TABLE friends ( first_name varchar(25), last_name varchar(25), gender enum(‘M’, ‘F’), grade smallint unsigned, hair_color varchar(20), email varchar(30), screen_name varchar(25), phone char(12));
• 6.
  Department of InformationTechnology 6Data base Technologies (ITB4201) Adding records to a table • Use the INSERT query: Example: INSERT INTO friends VALUES (‘Daffy’, ‘Duck’,‘M’,11,’Brunette’,‘daffy@disney.com’, YellowBill’, ’315-555-1213’);
• 7.
  Department of InformationTechnology 7Data base Technologies (ITB4201) Editing records • Use the UPDATE query: Example: UPDATE friends SET phone=‘315-555-1234’ WHERE first_name=‘Daffy’ and last_name = ‘Duck’;
• 8.
  Department of InformationTechnology 8Data base Technologies (ITB4201) Deleting records from a table • Use the DELETE query: Example: DELETE FROM friends WHERE first_name=‘Daffy’ and last_name=‘Duck’;
• 9.
  Department of InformationTechnology 9Data base Technologies (ITB4201) How to use a DB table? • Finding a date: SELECT first_name, screen_name, phone FROM friends WHERE gender=‘F’ and grade > 10 and hair_color=‘Red’;
• 10.
  Department of InformationTechnology 10Data base Technologies (ITB4201) Tutorial https://www.techonthenet.com/sql/select.php
• 11.
  Department of InformationTechnology 11Data base Technologies (ITB4201) Quiz 1. Which of the following SQL clauses is used to DELETE tuples from a database table? a) DELETE b) REMOVE c) DROP d) CLEAR 2. Which of the following is not a DDL command? a) UPDATE b) TRUNCATE c) ALTER d) None of the Mentioned 3. The command to remove rows from a table ‘CUSTOMER’ is __________________ a) DROP FROM CUSTOMER b) UPDATE FROM CUSTOMER c) REMOVE FROM CUSTOMER d) DELETE FROM CUSTOMER WHERE 4. With SQL, how do you select all the records from a table named “Persons” where the value of the column “FirstName” ends with an “a”? a) SELECT * FROM Persons WHERE FirstName=’a’ b) SELECT * FROM Persons WHERE FirstName LIKE ‘a%’ c) SELECT * FROM Persons WHERE FirstName LIKE ‘%a’ d) SELECT * FROM Persons WHERE FirstName=’%a%’ 5. SQL query to find all the cities whose humidity is 95. a) SELECT city WHERE humidity = 95 b) SELECT city FROM weather WHERE humidity = 95 c) SELECT humidity = 89 FROM weather d) SELECT city FROM weather
• 12.
  Department of InformationTechnology 12Data base Technologies (ITB4201) Quiz Answers 1. Which of the following SQL clauses is used to DELETE tuples from a database table? a) DELETE b) REMOVE c) DROP d) CLEAR 2. Which of the following is not a DDL command? a) UPDATE b) TRUNCATE c) ALTER d) None of the Mentioned 3. The command to remove rows from a table ‘CUSTOMER’ is __________________ a) DROP FROM CUSTOMER b) UPDATE FROM CUSTOMER c) REMOVE FROM CUSTOMER d) DELETE FROM CUSTOMER WHERE 4. With SQL, how do you select all the records from a table named “Persons” where the value of the column “FirstName” ends with an “a”? a) SELECT * FROM Persons WHERE FirstName=’a’ b) SELECT * FROM Persons WHERE FirstName LIKE ‘a%’ c) SELECT * FROM Persons WHERE FirstName LIKE ‘%a’ d) SELECT * FROM Persons WHERE FirstName=’%a%’ 5. SQL query to find all the cities whose humidity is 95. a) SELECT city WHERE humidity = 95 b) SELECT city FROM weather WHERE humidity = 95 c) SELECT humidity = 89 FROM weather d) SELECT city FROM weather