SQL Lecture Notes I PDF

NJIT MIS 385 SQL Lecture notes I I. Notes Introduction SQL is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements. Queries in SQL are made with a SEL...

NJIT MIS 385 SQL Lecture notes I I. Notes Introduction SQL is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements. Queries in SQL are made with a SELECT statement that starts with the clause SELECT. The SELECT statement is constituted by the following clauses in the following order: (Required:) SELECT DISTINCT (Optional:) (Required:) FROM (Optional:) WHERE (Optional:) GROUP BY (Optional:) HAVING (Optional:) ORDER BY (Required:) ; (semicolon to end the statement) We will use the Employee table as an example. The Employee table has the following columns: The simplest query would be: SELECT FROM ; For example: SELECT EmployeeID, LastName, FirstName FROM Employee; Marta Cavaleiro 2022. Protected content. Do not share, upload, or distribute. 1  The default syntax for specifying a column is TableName.ColumnName; but we can skip TableName for those columns that have unique names within the tables called in FROM. SQL clauses are not case sensitive. SELECT is the same as select. Also, you can break your code in multiple lines or have everything on the same line. Wildcard *: SELECT * FROM Employee; This simple example gets all the data in the table Employee (all columns and rows). The wildcard * is used when you want to get all the columns in the table(s) in FROM. Calculated fields/columns: In SELECT you can also make calculations using columns, called calculation fields. For example, in SELECT you could write "Quantity*UnitPrice", and it multiplies the Quantity and UnitPrice fields to make a new table column with the result. Dates are stored as a number of days, so it is possible to perform date arithmetic in a query too. This new column can be given a name by writing "AS name" after the expression (see “The AS Clause”). Sorting rows: ORDER BY If you want the data to appear in a specific order you need to use the “order by” clause. In front of ORDER BY usually follows a list of columns. After each field (and before the following comma if there is one), you can put ASC for ascending (alphabetically or smallest to largest) order, or DESC for descending order (backwards or largest to smallest). If you don't put either, SQL assumes ascending order. You can use calculated fields too and/or aggregate functions. The precedence of sorting is left-to-right as the fields are listed. For example: ORDER BY Department ASC, JobRating DESC will order employees first by Department (from A to Z), and then, within each set of records with the same Department name, it will order by JobRating in descending order. Filtering rows WHERE The WHERE clause is used to extract only those records that fulfill a specified criterion. The syntax is as follows: WHERE logical_conditions; Example: SELECT * FROM Employee WHERE Salary >= 50000; Marta Cavaleiro 2022. Protected content. Do not share, upload, or distribute. 2 The logical conditions in WHERE are comparisons (like "=" or "= e.g. WHERE HireDate >= #12/31/2019# ‘2019-12-31’ Table / column names with spaces Use square brackets […] Use backticks `…` or special e.g. SELECT [purchase date] e.g. SELECT `purchase date` characters Use square brackets […] Use single quotes ‘…’ Aliases with spaces e.g. SELECT Quant*Price AS e.g. SELECT Quant*Price AS [Order Total] ‘Order Total’ Marta Cavaleiro 2022. Protected content. Do not share, upload, or distribute. 5  Ms Access MySQL * - The star sign represents zero, % - The percentage sign represents one, or multiple characters zero, one, or multiple characters LIKE wildcards ? - The question mark represents a _ - The underscore represents a single character single character Marta Cavaleiro 2022. Protected content. Do not share, upload, or distribute. 6