IM101 - REVIEWER FINALS (1) - SQL Tutorial PDF

LESSON 11 SQL (Structured Query Language) How SQL Types Help Fits into four broad categories : Data types define the characteristics of the data that can be stored in a locati...

LESSON 11 SQL (Structured Query Language) How SQL Types Help Fits into four broad categories : Data types define the characteristics of the data that can be stored in a location such as a database It is a data definition language (DDL): SQL column. A data type defines the possible set of values includes commands to create database objects that are accepted. For example, a type of INT which such as tables, indexes, and views, as well as stands for integer in SQL server can only accept whole commands to define access rights to those numbers, decimal values are not allowed. database objects. Examples are Here is a listing of some people and their ages It is a data manipulation language (DML): SQL includes commands to insert, update, delete, and retrieve data within the database tables. Figure 10.1 Sample Table with different values for Age column Can you see the issue with these ages? They’re all in an inconsistent format. Not only are they hard to read, but sorting and calculating the data is difficult. By using SQL Server data types, which enforce the expected type of data to be stored, we can achieve a much better result. Here is the same table with the ages shown as integers What are SQL Server Data Types? Before we get into the data types used in SQL server, I think it is important for everyone to understand what a data type is, and why they are needed for use in computers. Figure 10.2 Sample Table with INT datatype for Age For our example, we’ll talk about a person. If you were column thinking about the information you could use to describe a person you may think to collect their name, birthdate, weight, and number of children. For each of these attributes, the data generally falls into several When age is defined as an integer the expectation is categories. data are entered as whole numbers. Names are stored as text, birthdates as calendar dates, Also, without getting into technical details, you can see and weight as decimal values, and a number of children storing the age as an integer takes up much less space as integers. Each of these categories of values is a data than the age in the first table. This may not seem like a type. huge consideration with small databases, but when working with data on smartphones or “big data” Practical uses of the INT data type include using it to scenarios, these considerations add up. count values, store a person’s age, or use as an ID key to a table. Once the system understands the data type, it can then sort the data in an order that makes sense as well as But INT wouldn’t be so good to keep track of a terabyte perform calculations. hard drive address space, as the INT data type only goes to 2 billion and we would need to track into the trillions. For this, you could use BIGINT. Reasons to use SQL Server Data Types The INT data type is use within calculations. Since Here are some reasons why data types are important: DaysToManufacture is defined as INT we can easily calculate hours by multiplying it by 24: 1. Data is stored in a consistent and known format. SELECT Name, 2. Knowing the data type allows you to know DaysToManufacture, which calculations and formulations you can use on the column. DaysToManufacture * 24 as HoursToManufacture 3. Data types affect storage. Some values take FROM Production.Product up more space when stored in one data type versus another. Take our age tables above Here you can see the results for example. 4. Data types affect performance. The less time the database has to infer values or convert them the better. “Is December 32, 2015 a date?” Commonly used SQL Server Data Types In SQL you define what kind of data to store within columns. Some example includes text or numeric data. This is the column’s data type, and one of its most important properties as it alone determines whether a value is valid for storage in a column. There are over thirty different data types you can choose from when defining columns, some have specific uses, such as storing images. In this article we’re going to cover the seven data types you’ll most frequently encounter in your everyday use of Figure 10.3 Sample Table calculation SQL. These are: INT VARCHAR, NVARCHAR There are many operations and functions you can use with integers which we’ll cover once we dig into DATETIME functions. DECIMAL, FLOAT BIT VARCHAR and NVARCHAR – Text Values Use VARCHAR and NVARCHAR to store variable length INT – Integer Data Type text values. “VARCHAR” stands for variable length character. The integer data type stores whole numbers. Examples include -23, 0, 5, and 10045. Whole numbers don’t Specify the number of characters to store in a include decimal places. Since the SQL server uses a VARCHAR or NVARCHAR as the column is defined. number of computer words to represent an integer there Notice how Name is defined to hold fifty characters: are maximum and minimum values that it can represent. An INT datatype can store a value from -2,147,483,648 to 2,147,483,647.  you a glimpse, we’ll use the YEAR function to count employees hired each year. When given a DATETIME value, the YEAR function returns the year. The query we’ll use is SELECT YEAR(HireDate) HireDateYear, Count(*) HireDateCount FROM HumanResources.Employee Figure 10.4 Sample varchar column shown in MSSQL Mngt Studio GROUP BY YEAR(HireDate) ORDER BY YEAR(HireDate) What makes VARCHAR popular is that values less than fifty characters take less space. Space is allocated as The benefit is the DATETIME type ensures the values needed. This differs from the CHAR data type which are valid dates. Once this is assured, we’re able to use always allocates the specified length, regardless of the a slew of functions to calculate the number of days length of the actual data stored. between dates, the month of a date and so on. The VARCHAR datatype can typically store a maximum of 8,000 characters. Use the NVARCHAR datatype to DECIMAL and FLOAT – Decimal Points store Unicode text. Since UNICODE characters occupy twice the space, NVARCHAR columns can store a Use both DECIMAL and FLOAT datatypes to work with maximum of 4,000 characters. decimal values such as 10.3. The advantage NVARCHAR has over VARCHAR is it I lumped DECIMAL and FLOAT into the same category can store Unicode characters. This makes it handy to since they both can handle values with decimal points; store extended character sets like those used for however, they both do so differently: languages such as Kanji. If you need precise values, such as when working with Databases designed prior to SQL 2008 typically use financial or accounting data, then use DECIMAL. The VARCHAR; however, more modern databases or those reason is the DECIMAL datatype allows you to define global in nature tend to use NVARCHAR. the number of decimal points to maintain. DATETIME – Date and Time DECIMAL Use the DATETIME data type to store the date and time. An example of a DATETIME value is yy-mm-dd DECIMAL data types are defined by precision and scale. The precision determines the number of total digits to store; whereas, scale determines the number of 1968-10-23 1:45:37.123 digits to the right of the decimal point. A DECIMAL datatype is specified as rd This is the value for October 23 , 1968 at 1:45 AM. DECIMAL(precision,scale). Actually the time is more precise than that. The time is A DECIMAL datatype can be no more than 38 digits. really 45 minutes, 37.123 seconds. The precision and scale must adhere to the following In many cases, you just need to store the date. In these relation cases, the time component is zeroed out. Thus, November 5th, 1972 is 0 500 AND P_CAT = 'CARS') SELECT * FROM tbl_Products ROLLBACK command – undo any changes to the made WHERE (P_PRICE > 500 OR P_CAT = 'CARS') since the last COMMIT command.It should be executed before any COMMIT command is made, otherwise, all SELECT * FROM tbl_Products changes would be made final. WHERE NOT(P_CAT = 'CARS')  Notice that for the entire expression to be true, either A or B can be true. We can use this to our favor when writing queries. Consider the situation where you may need to match a column on one or more values. This query returns People having a FirstName of either Gail or John: SELECT BusinessEntityId, PersonType, FirstName, LastName FROM Person WHERE FirstName = 'Gail' OR FirstName = 'John' Things to consider when using SQL OR: Figure 13.1 AND Truth table When repeating OR clauses for the same field consider using the IN operator. It is The SQL AND operator returns a TRUE only if all shorter, and easier to read. conditions are also TRUE. The following truth table Be careful mixing AND and OR operators shows all combinations of values for the condition (A together. Much like when you work with AND B). multiplication and addition, the operations follow a prescribed evaluation order. Notice that for the entire expression to be true, both A When in doubt, use parenthesis. Place them and B must be true. We can use this to our favor around the OR clauses to ensure the when writing queries where one or more columns must Boolean conditions are properly evaluated. match to be included in a result. This query returns every Person whose first name is John and with a PersonType of EM. The SQL NOT operator returns the opposite of the Boolean value it precedes. The following truth table SELECT BusinessEntityId, PersonType, shows all combinations of values for the condition (NOT FirstName, LastName A) FROM Person WHERE PersonType = 'EM' AND FirstName = 'John' The SQL OR operator returns a TRUE if either condition is TRUE. The following truth table shows all combinations of values for the condition (A OR B). Figure 13.2 NOT Truth table Notice that for the whenever A is TRUE the result is FALSE, and vice versa. We can use this to our favor when writing queries. Consider the situation where you may need to match the opposite of a condition… This query returns People not having a FirstName or Gail or John: SELECT BusinessEntityId, PersonType, Figure 13.2 OR Truth table FirstName, LastName FROM Person  WHERE NOT (FirstName = 'Gail' or FirstName It this example see how BETWEEN simplifies selecting = 'John') every Person whose record was between January 12th – 14th, 2014 date range. Special Operators SELECT FirstName, LastName, ModifiedDate BETWEEN: Used to check whether an attribute value is FROM Person within a range. For example if we want to list all product WHERE ModifiedDate BETWEEN '2014-01-12' AND '2014-01-14' information whose prices is between $5 and $100, we can have an SQL of the following: In summary, use the SQL BETWEEN operator to test SELECT * FROM tbl_Products against a range of values. The range is inclusive. Using SQL BETWEEN is shorthand for using >= AND 50 and P_PRICE < 100 WHERE VENDOR IS NULL Use the SQL BETWEEN to test whether a column value is within a range of specified values. You can use the SQL BETWEEN operator with a variety of types, such as integer, varchar, and dates. LIKE: Used to check whether an attribute value matches a given string pattern. We can use % to indicate any and all following or preceding characters are eligible, while _ means any one character can be substituted for the underscore. Access uses * and ? for % and _.Example : SELECT * from tbl_Name WHERE LAST_NAME like 'John%' --this sample might include Johnson,Johnsen SELECT * from Person Figure 13.3 Sample table WHERE LAST_NAME like '%John' --this sample might include LoJohn,PaulJohn The following SQL shows how to use BETWEEN to find all people having last names greater than or equal to SELECT * from Person Colvin and less than or equal to Conteras. WHERE ID like '_01_' --this sample might include A01A,A01B,B01B, SELECT FirstName, LastName etc... FROM Person WHERE LastName BETWEEN 'Colvin' and SELECT * from Person 'Contreras' WHERE LAST_NAME like 'John_' --this sample might include Johny,Johns, etc… Using BETWEEN improves the readability of your SQL. Notice it eliminates several comparison operators. The The Like Operator determines whether specified string, above example is equivalent to: such as column value, matches a pattern. The strength in the LIKE operator lies within its pattern. SELECT FirstName, LastName As you see later on, it consists of regular characters and wildcard characters. The regular character must exactly FROM Person match their counterpart value. However, the wildcards WHERE LastName >= 'Colvin' AND allow one or more matches. Using patterns makes LIKE more flexible than ordinary = LastName 250 ORDER BY yearid,teamid; SQL JOIN Example HAVING Clause Expressions Suppose we want to see which orders shipped and the You can also do calculation with the aggregate function employee that wrote them. with the SQL HAVING. In this example we’re returning As you can see from the example below all batting seasons where 4.5% of the at bats results in the LastName isn’t stored in the Orders table home runs: So, without joins, all we can hope to include is the EmployeeID. That’s not very user friendly. SELECT yearID, What we really need is a way to display the LastName. teamID, SUM(ab) AS [At Bats], SUM(hr) AS [Home Runs] FROM Batting GROUP BY yearid, teamid HAVING (CAST(Sum(hr) as float) / CAST(Sum(ab) as float)) >= 0.045 ORDER BY yearid, teamid;  Outer Joins Given the contents of the PRODUCT and VENDOR tables, the following left outer join will show all VENDOR rows and all matching PRODUCT rows: SELECT P_CODE, VENDOR.V_CODE, V_NAME FROM VENDOR LEFT JOIN PRODUCT ON VENDOR.V_CODE=PRODUCT.V_CODE The right outer join will join both tables and show all Figure 15.1 Joining table product rows with all matching vendor rows.The SQL command for the right outer join is: If you’re following along the red arrows, you’ll see we can use the EmployeeID to match Orders with SELECT PRODUCT.P_CODE, VENDOR.V_CODE, Employees. V_NAME Using database joins, known as INNER JOINs, we can FROM VENDOR RIGHT JOIN PRODUCT match values from each table. This allows us to include columns from the matching rows in our result. ON VENDOR.V_CODE=PRODUCT.V_CODE Now we have a means to display LastName alongside ShippedDate. To do this with an INNER JOIN we would write: UNION Perfect tool for combining a common list of data – one SELECT Employees.LastName, Orders.ShippedDate that excludes duplicate records. The UNION statement FROM Orders combines rows from two or more queries without including duplicate rows. The syntax of the INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID UNION statement is: query UNION query If you look carefully, you’ll see in this example we’re In other words, the UNION statement combines the matching from Orders to Employees. output of two SELECT queries. (Remember that the As the join executes, SELECT statements must be union-compatible. That is, each Orders.EmployeeID matches to Employees.Emplo they must return the same number of attributes and yeeID. similar data types.) From here the matching Employees.LastName displays within the results. Example select ck_name,ck_prc from tbl_Cake Lesson 16 UNION Recursive Joins select fd_name,fd_prc from tbl_Foodies An alias is especially useful when a table must be joined Result: to itself in a recursive query. Using the data in the EMP tbl_Cake table, you can generate a list of all employees with their managers’ names by joining the EMP table to itself. In that case, you would also use aliases to differentiate the table from itself. The SQL command sequence would look like this: SELECT E.EMP_MGR, M.EMP_LNAME, E.EMP_NUM, E.EMP_LNAME UNION FROM EMP E, EMP M WHERE E.EMP_MGR=M.EMP_NUM ORDER BY E.EMP_MGR = The relational join operation merges rows from two tables and returns the rows with one of the following conditions: Have common values in common columns (natural join). Meet a given join condition (equality or inequality). Have common values in common columns or UNION ALL have no matching values (outer join). Will keep all the rows from the given queries including Join operations can be classified as inner joins and outer the duplicates joins.The inner join is the traditional join in which only rows that meet a given criteria are selected. The join select ck_name,ck_prc from tbl_Cake criteria can be an equality condition (also called a natural UNION ALL join or an equijoin) or an inequality condition (also called a theta join).An outer join returns not only the matching select fd_name,fd_prc from tbl_Foodies rows but also the rows with unmatched attribute values Result: for one table or both tables to be joined. The SQL standard also introduces a special type of join, called a tbl_Cake cross join, that returns the same result as the Cartesian product of two sets or tables. CROSS JOIN UNION ALL performs a relational product (also known as the Cartesian product) of two tables. The cross join syntax is: SELECT column-list FROM table1 CROSS JOIN = table2 select * from tbl_Cake CROSS JOIN tbl_Foodies Result INTERSECT The INTERSECT statement can be used to combine rows from two queries, returning only the rows that appear in both sets. The syntax for the INTERSECT statement is: query INTERSECT query Example : select ck_name,ck_prc from tbl_Cake INTERSECT select fd_name,fd_prc from tbl_Foodies Result JOIN ON Another way to express a join when the tables have no common attribute names is to use the JOIN ON operand. That query will return only the rows that meet the indicated join condition. The join condition will SQL JOIN OPERATOR typically include an equality comparison expression of two columns. (The columns may or may not share the same name but, obviously, must have comparable data FROM table1 RIGHT[OUTER] JOIN table2 ON types.) The syntax is: join-condition SELECT column-list FROM table1 JOIN table2 ON join-condition select c.ck_name,c.ck_prc,f.fd_name,fd_prc from tbl_Cake c select c.ck_name,c.ck_prc,f.fd_name,fd_prc right JOIN tbl_Foodies f ON f.fd_id = c.ck_id from tbl_Cake c result JOIN tbl_Foodies f ON f.fd_id = c.ck_id result OUTER JOIN The full outer join returns not only the rows matching the join condition (that is, rows with matching values in the An outer join returns not only the rows matching the join common column) but also all of the rows with unmatched condition (that is, rows with matching values in the values in either side table. The syntax is: common columns) but also the rows with unmatched values. The ANSI standard defines three types of outer SELECT column-list joins: left, right, and full. The left and right designations FROM table1 FULL[OUTER] JOIN table2 ON reflect the order in which the tables are processed by the join-condition DBMS. Remember that join operations take place two tables at a time.The first table named in the FROM clause will be the left side, and the second table named select c.ck_name,c.ck_prc,f.fd_name,fd_prc will be the right side. If three or more tables are being from tbl_Cake c joined, the result of joining the first two tables becomes the left side, and the third table becomes the right side. FULL JOIN tbl_Foodies f ON f.fd_id = c.ck_id The left outer join returns not only the rows matching the Result join condition (that is, rows with matching values in the common column) but also the rows in the left side table with unmatched values in the right side table. The syntax is: SELECT column-list FROM table1 LEFT[OUTER] JOIN table2 ON join-condition select c.ck_name,c.ck_prc,f.fd_name,fd_prc from tbl_Cake c left JOIN tbl_Foodies f ON f.fd_id = c.ck_id result Select Subquery The right outer join returns not only the rows matching the join condition (that is, rows with matching values in Figure 16.1 Select Subquery the common column) but also the rows in the right side table with unmatched values in the left side table. The syntax is: Example : SELECT column-list Insert  insert into tbl_Cake (ck_name,ck_prc) must return a character string. Also, if the query returns more than a single value, the DBMS will generate an select fd_name,fd_prc from tbl_Foodies error. Subqueries can also be used in combination with update joins.For example, the following query lists all of the customers who ordered the product “Claw hammer”: update tbl_Cake set ck_prc = (select MIN(ck_prc) from tbl_Cake) SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME FROM CUSTOMER JOIN where ck_id in (select fd_id from tbl_Foodies) INVOICE USING (CUS_CODE) JOIN LINE USING (INV_NUMBER) JOIN delete PRODUCT USING (P_CODE) delete from tbl_Cake WHERE P_CODE=(SELECT P_CODE FROM PRODUCT WHERE P_DESCRIPT=‘Claw where ck_id in (select fd_id from tbl_Foodies) hammer’); To be precise, the subquery can return: One single value(one column and one row). This IN subqueries subquery is used anywhere a single value is expected, as in the right side of a comparison When you want to compare a single attribute to a list of expression (such as in the preceding UPDATE values, you use the IN operator. example when you assign the average price to When the P_CODE values are not known beforehand, the product’s price). Obviously, when you assign but they can be derived using a query, you must use an a value to an attribute, that value is a single IN subquery. The following example lists all customers value, not a list of values. Therefore, the who have purchased hammers, saws, or saw blades. subquery must return only one value (one column, one row). If the query returns multiple SELECT DISTINCT CUS_CODE, CUS_LNAME, values, the DBMS will generate an error. CUS_FNAME FROM CUSTOMER JOIN INVOICE USING (CUS_CODE) A list of values(one column and multiple rows). This type of subquery is used anywhere a list of JOIN LINE USING (INV_NUMBER) JOIN values is expected, such as when using the IN PRODUCT USING (P_CODE) clause (that is, when comparing the vendor code WHERE P_CODE IN ( SELECT P_CODE to a list of vendors). Again, in this case, there is FROM PRODUCT only one column of data with multiple value instances.This type of subquery is used WHERE P_DESCRIPT LIKE '%hammer%' OR frequently in combination with the IN operator in P_DESCRIPT LIKE '%saw%') a WHERE conditional expression. A virtual table (multicolumn, multirow set of Correlated Subqueries values). This type of subquery can be used anywhere a table is expected, such as when is a subquery that executes once for each row in the using the FROM clause. outer query. That process is similar to the typical nested loop in a programming language. For example: The most common type of subquery uses an inner SELECT subquery on the right side of a WHERE FOR X=1 TO 2 comparison expression.For example, to find all products with a price greater than or equal to the average product FOR Y=1 TO 3 price, you write the following query: PRINT “X=“X, “Y=“Y SELECT P_CODE, P_PRICE FROM PRODUCT END WHERE P_PRICE >= (SELECT AVG(P_PRICE) END FROM PRODUCT); will yield the output: Note that this type of query, when used in a>, =, or