Database Management System II 1 Chapter 2 Data

Database Management System - II 1

Chapter# 2 Data Types, Operators Expressions and SQL Joins 2

SQL Data Types SQL data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQL. Exact Numeric Data Types: 3

SQL Data Types Approximate Numeric Data Types: Date and Time Data Types: 4

SQL Data Types Character Strings Data Types: 5

SQL Operators An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement. Ø Ø Arithmetic operators Comparison operators Logical operators Operators used to negate conditions 6

SQL Operators SQL Arithmetic Operators: Assume variable a holds 10 and variable b holds 20, then: 7

SQL Operators SQL Comparison Operators: Assume variable a holds 10 and variable b holds 20, then: 8

SQL Operators SQL Comparison Operators: Assume variable a holds 10 and variable b holds 20, then: 9

SQL Operators SQL Logical Operators: Here is a list of all the logical operators available in SQL. 10

SQL Expressions Ø An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. Ø SQL EXPRESSIONs are like formulas and they are written in query language. You can also use them to query the database for specific set of data. Syntax: SELECT column 1, column 2, column. N FROM table_name WHERE [CONDITION|EXPRESSION]; 11

SQL Expressions SQL - Boolean Expressions: Ø SQL Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax: SELECT column 1, column 2, column. N FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION; Example: SELECT * FROM CUSTOMERS WHERE SALARY = 10000; 12

SQL Expressions SQL - Numeric Expression: Ø This expression is used to perform any mathematical operation in any query. Following is the syntax: SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ; Example: SELECT (15 + 6) AS ADDITION SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS; 13

SQL Expressions SQL - Date Expressions: Ø Date Expressions return current system date and time values: Exmple: SELECT GETDATE(); 14

Joins in SQL Server Ø Ø Joins in SQL Server is used to retrieve data from two or more related tables. In general tables are related to each other using foreign key constraints. In SQL Server, the are different types of JOINS § INNER JOIN § OUT JOIN § CROSS JOIN OUT Joins are divided into: § Left Join or Left Outer Join § Right Join or Right Outer Join § Full Join or Full Outer Join 15

Inner Join or Join Ø Ø INNER JOIN returns only the matching rows between both the tables. Non matching rows are eliminated. tbl. Employee ID Name Gender Salary Department. ID 1 Ahmad Male 7000 2 2 Akbar Male 10000 1 3 Ajmal Male 3000 1 4 Sara Female 7000 Null 5 Imran Male 30000 Null tbl. Department ID Dept_Name Location Dept_Head 1 HR Kabul Yusuf 2 IT Mazar Khan 3 Finance Herat Omer SELECT Name, Gender, Salary, Dept_Name FROM tbl. Employee INNER JOIN tbl. Department ON tbl. Employee. Department. ID=tbl. Department. ID OR SELECT Name, Gender, Salary, Dept_Name FROM tbl. Employee JOIN tbl. Department ON tbl. Employee. Department. ID=tbl. Department. ID 16

Left Outer Join or Left Join Ø LEFT JOIN returns all the matching rows plus non-matching rows from the left table. tbl. Employee ID Name Gender Salary Department. ID 1 Ahmad Male 7000 2 2 Akbar Male 10000 1 3 Ajmal Male 3000 1 4 Sara Female 7000 Null 5 Imran Male 30000 Null tbl. Department ID Dept_Name Location Dept_Head 1 HR Kabul Yusuf 2 IT Mazar Khan 3 Finance Herat Omer SELECT Name, Gender, Salary, Dept_Name FROM tbl. Employee LEFT OUTER JOIN tbl. Department ON tbl. Employee. Department. ID=tbl. Department. ID OR SELECT Name, Gender, Salary, Dept_Name FROM tbl. Employee LEFT JOIN tbl. Department ON tbl. Employee. Department. ID=tbl. Department. ID 17

Right Outer Join or Right Join Ø RIGHT JOIN returns all the matching rows plus non-matching rows from the right table. tbl. Employee ID Name Gender Salary Department. ID 1 Ahmad Male 7000 2 2 Akbar Male 10000 1 3 Ajmal Male 3000 1 4 Sara Female 7000 Null 5 Imran Male 30000 Null tbl. Department ID Dept_Name Location Dept_Head 1 HR Kabul Yusuf 2 IT Mazar Khan 3 Finance Herat Omer SELECT Name, Gender, Salary, Dept_Name FROM tbl. Employee RIGHT OUTER JOIN tbl. Department ON tbl. Employee. Department. ID=tbl. Department. ID OR SELECT Name, Gender, Salary, Dept_Name FROM tbl. Employee RIGHT JOIN tbl. Department ON tbl. Employee. Department. ID=tbl. Department. ID 18

Full Outer Join or Full Join Ø FULL JOIN returns all rows from both the right and left tables, including the non-matching rows tbl. Employee ID Name Gender Salary Department. ID 1 Ahmad Male 7000 2 2 Akbar Male 10000 1 3 Ajmal Male 3000 1 4 Sara Female 7000 Null 5 Imran Male 30000 Null tbl. Department ID Dept_Name Location Dept_Head 1 HR Kabul Yusuf 2 IT Mazar Khan 3 Finance Herat Omer SELECT Name, Gender, Salary, Dept_Name FROM tbl. Employee FULL OUTER JOIN tbl. Department ON tbl. Employee. Department. ID=tbl. Department. ID OR SELECT Name, Gender, Salary, Dept_Name FROM tbl. Employee FULL JOIN tbl. Department ON tbl. Employee. Department. ID=tbl. Department. ID 19

Cross Join Ø A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. tbl. Employee ID Name Gender Salary Department. ID 1 Ahmad Male 7000 2 2 Akbar Male 10000 1 3 Ajmal Male 3000 1 4 Sara Female 7000 Null 5 Imran Male 30000 Null tbl. Department ID Dept_Name Location Dept_Head 1 HR Kabul Yusuf 2 IT Mazar Khan 3 Finance Herat Omer SELECT Name, Gender, Salary, Dept_Name FROM tbl. Employee FULL OUTER JOIN tbl. Department Cross Join doesn’t have ON clause SELECT COLUMN_LIST FROM LEFT_TABLE_NAME JOIN_TYPE RIGHT_TABLE_NAME ON JOIN_CONDITION 20

Advanced or Intelligent Joins ID Name Gender Salary Department. ID 1 Ahmad Male 7000 2 2 Akbar Male 10000 1 3 Ajmal Male 3000 1 4 Sara Female 7000 Null 5 Imran Male 30000 Null ID Dept_Name Location Dept_Head 1 HR Kabul Yusuf 2 IT Mazar Khan 3 Finance Herat Omer 21

Self Join To join a table with itself is called Self Join can be classified as Ø Ø § § § Inner Self Join Outer Self Join(Left, Right and Full) Cross Self Join Employee. ID Name Manager. ID Employee Manager 1 Ahmad 2 Ahmad Yunus 2 Yunus 3 Yunus Ajmal 3 Ajmal 1 Ajmal Ahmad 4 Tahir 3 Tahir Ajmal 5 Imran NULL 6 Omer 2 Omer Yunus 7 Nasir 3 Nasir Ajmal SELECT E. Name AS Employee, M. Name AS Manager FROM tbl. Employee E LEFT JOIN tbl. Employee M ON E. Manager. ID=M. Employee. ID SELECT E. Name AS Employee, M. Name AS Manager FROM tbl. Employee E INNER JOIN tbl. Employee M ON E. Manager. ID=M. Employee. ID SELECT E. Name AS Employee, M. Name AS Manager FROM tbl. Employee E CROSS JOIN tbl. Employee M 22