Sql And Database Management Concepts PDF

SQL – DML - Queries CMPS 342 Database Systems Spring 2022 Database Systems 1  SQL DDL DML DCL TCL Defin...

SQL – DML - Queries CMPS 342 Database Systems Spring 2022 Database Systems 1  SQL DDL DML DCL TCL Define Schemas, create roles, Transaction Relations, Domains, Query Data permissions, access specific Views CREATE Update Data DROP ALTER TRUNCATE Database Systems 2 Manipulating the Database by Querying Data Main command is used for data retrieval SELECT Formed as a SELECT FROM WHERE Block Database Systems 3 Querying Data SELECT projection_attribute(s) FROM table_name(s) WHERE selection_join_condition ORDER BY attribute_name(s) SELECT and FROM are mandatory SELECT attributes to be retrieved FROM corresponding tables WHERE a condition is met and ORDER them BY a specific order Database Systems 4 Query with Selection Condition SELECT Bdate, Address FROM EMPLOYEE WHERE Fname = ‘John’ AND Minit = ‘B’ AND Lname = ‘Smith’; Database Systems 5 Select-Project-Join Query SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT WHERE Dname = ‘Research’ AND Dnumber = Dno; Database Systems 6 Select-Project-Join Query (two joins) SELECT Pnumber, Dnum, Lname, Address, Bdate FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Plocation = ‘Stafford’ Database Systems 7 Ambiguous Attribute Names Same name can be used for two attributes in different tables ➔ Qualify the attribute name with the relation name relation.attribute Database Systems 8 Ambiguous Attribute Names Suppose Lname in EMPLOYEE was Name and Dno was Dnumber Database Systems 9 Aliasing Rename table names to shorter names by creating an alias for each table name Avoid repeated typing of long table names Results in queries that are easier to comprehend Can also be applied to attributes RELATION or Attribute AS Xx alias Database Systems 10 Aliasing Database Systems 11 Unspecified WHERE Clause Missing WHERE clause indicates no condition in tuple selection All tuples of the relation are selected If more than one relation is included in FROM, what happens? ➔ All possible tuple combinations of the relations is shown Database Systems 12 Unspecified WHERE Clause Database Systems 13 Use of the Asterisk Retrieve all the attribute values of any EMPLOYEE who works in DEPARTMENT number 5 Database Systems 14 Use of the Asterisk What would these queries output? Database Systems 15 Duplicate Tuples Duplicate tuples can appear as a result of a query Use the keyword DISTINCT in the SELECT clause Database Systems 16 Pattern Matching Allow comparison conditions on only parts of a character string using the LIKE command WHERE Attribute LIKE ‘%ring’ WHERE Attribute LIKE ‘s_ _ _ _ g’ % replaces zero or more characters _ replaces one character Database Systems 17 Pattern Matching Retrieve all employees whose address is in Houston, Texas Find all employees who were born during the 1970s. Database Systems 18 Arithmetic Operators SQL allows the use of arithmetic in queries For numeric values or attributes with numeric domains add (+), subtract (-), multiply (*), divide (/) Between (BETWEEN) For string datatypes Append (||) Database Systems 19 Arithmetic Operators Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10% raise. Database Systems 20 Arithmetic Operators Retrieve all employees in department 5 whose salary is between $30,000 and $40,000. Database Systems 21 NULL Values Represent missing data Unknown Unavailable Not applicable SQL doesn’t distinguish among the different meanings If involved in a condition, we don’t know if it’s TRUE or FALSE ➔Consider as UNKNOWN ➔ SQL uses a three-valued logic instead of binary logic Database Systems 22 Logical connectives for TRUE, FALSE, UNKOWN Database Systems 23 Checking if value is NULL Use comparison operator IS or IS NOT instead of = or Retrieve the names of all employees who do not have supervisors. Database Systems 24 Ordering Query Results Allow user to order tuples by values of attribute(s) Default order is ascending You can specify keyword DESC for descending Add after the WHERE clause ORDER BY attribute_name(s) ASC/DESC Database Systems 25 Ordering Query Results Retrieve a list of employees and the projects they are working on, ordered by decreasing department and, within each department, ordered alphabetically by last name, then first name. Database Systems 26  27 Break Database Systems Nested Queries Existing values in database fetched and then used in a comparison condition Complete select-from-where blocks within another SQL query Inner query Outer query Can appear in WHERE or FROM or other SQL clauses as needed Database Systems 28 Nested Queries Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project. or Database Systems 29 Nested Queries with multiple attributes Select the Essns of all employees who work the same (project, hours) combination on some project that employee ‘John Smith’ (whose Ssn = ‘123456789’) works on. Database Systems 30 Comparison Operators IN compares a single value with a set of values Equivalent to = ANY combine (=, >, >=,