Database Management System DBMS -SQL queries

Database Management System DBMS -SQL queries

• 1.
  Basic Structure ofSQL Queries Mrs.R.SABITHA .,M.Sc.,M.Phil., Assistant Professor, Department of Computer Science(SF) V.V.Vanniaperumal College for Women, Virudhunagar.
• 2.
   Overview ofthe SQL Query Language  Data Definition  Basic Query Structure  Additional Basic Operations  Set Operations  Null Values  Aggregate Functions  Nested Sub queries
• 3.
  Overview of theSQL Query Language • SQL is a language to operate databases; it includes Database Creation, Database Deletion, Fetching Data Rows, Modifying & Deleting Data rows, etc. • SQL stands for Structured Query Language which is a computer language for storing, manipulating and retrieving data stored in a relational database.
• 4.
  SQL Basic Commands DDL(Data Definition Languages) 1. Create - Creates a new table, a view of a table, or other object in the database. 2. Alter - Modifies an existing database object, such as a table. 3. Drop - Deletes an entire table, a view of a table or other objects in the database.
• 5.
  SQL Basic Commands DML- Data Manipulation Language 1. SELECT - Retrieves certain records from one or more tables. 2. Update – Modify records 3 .Delete – Delete Records
• 6.
  Basic Query Structure A typical SQL query has the form: select A1, A2, ..., An from r1, r2, ..., rm where P  Ai represents an attribute  Ri represents a relation  P is a predicate.  The result of an SQL query is a relation.
• 7.
  Select Clause The selectclause lists the attributes desired in the result of a query corresponds to the projection operation of the relational algebra Example: find the names of all instructors: select name from instructor NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.) E.g., Name ≡ NAME ≡ name
• 8.
  Select Clause SQL allowsduplicates in relations as well as in query results. To force the elimination of duplicates, insert the keyword distinct after select. Find the department names of all instructors, and remove duplicates select distinct dept_name from instructor The keyword all specifies that duplicates should not be removed. select all dept_name from instructor
• 9.
  The from Clause The from clause lists the relations involved in the query  Corresponds to the Cartesian product operation of the relational algebra.  Find the Cartesian product instructor X teaches select X from instructor, teaches  generates every possible instructor – teaches pair, with all attributes from both relations.  For common attributes (e.g., ID), the attributes in the resulting table are renamed using the relation name (e.g., instructor.ID)
• 10.
  The where Clause The where clause specifies conditions that the result must satisfy  Corresponds to the selection predicate of the relational algebra.  To find all instructors in Comp. Sci. dept select name from instructor where dept_name = ‘Comp. Sci.'  To find all instructors in Comp. Sci. dept with salary > 80000 select name from instructor where dept_name = ‘Comp. Sci.' and salary > 80000
• 11.
  Examples  Find thenames of all instructors who have taught some course and the course_id  select name, course_id from instructor , teaches where instructor.ID = teaches.ID  Find the names of all instructors in the Art department who have taught some course and the course_id  select name, course_id from instructor , teaches where instructor.ID = teaches.ID and instructor. dept_name = ‘Art’
• 12.
  ThankYou