IM SQL

• SQL – Structured Query Language
• Structured Query Language -Standard language for creating and querying relational databases
• Classification of SQL commands:

  1. Data Definition Language (DDL)
  2. Data Manipulation Language (DML)
  3. Data Control Language (DCL)

  access the database or particular objects within the database; used to

  store or remove transactions that would affect the database
• Data Definition Language (DDL) – used to create, alter, and drop tables
• Data Manipulation Language (DML) – core commands of SQL used for

  updating, inserting, modifying, and querying the data in the database
• Data Control Language (DCL) – used to grant or revoke privileges to

  access the database or particular objects within the database; used to

  store or remove transactions that would affect the database
• ❑SELECT – allows users to query the data contained in the tables based on certain criteria
• Structure of SQL Query

  SELECT

  FROM

  WHERE

  GROUP BY

  HAVING

  ORDER BY
• • SELECT (project siya sa relational algebra)selects columns, attributes, literals, arithmetic expressions

  SELECT studid, studname

  FROM table_1
• SELECT columns or attributes

  literals

  arithmetic expression

  scalar or column functions

  FROM tables or views

  WHERE condition for row selection

  ORDER BY sort key
• if you want to display all the attributes do this:

  SELECT *

  FROM Product_Table;
• Display prod id, description, and prod_finish of attributes with finish of red

  SELECT prod id, description, prod_finish

  FROM Product_T

  WHERE prod_finish = 'red';

  
• Display the prod id and description of those whose price is less than 400

  SELECT prod_id, prod_desc

  FROM Product_T

  WHERE standard_price<400;
• Display prod id and description of those whose finish is red. Sort by

  description

  SELECT prod_id, description

  FROM Product_T

  WHERE prod_finish='red'

  ORDER BY prod_desc;
• Display prod id and description. Sort by prod_line_id and price

  SELECT prod_id, prod_desc

  FROM Product_T

  ORDER BY prod_line_id, standard_price;
• USING BOOLEAN OPERATORS WITH SELECT CLAUSE

  AND – joins 2 or more conditions ; only when all conditions are true

  OR – joins 2 or more conditions ; when any conditions are true

  NOT – negates an expression
• If multiple boolean operators are used in an SQL statement, NOT is evaluated first, then AND, then OR.
• give the id of selected 3,4,5

  SELECT *

  FROM Product_T

  WHERE Standard_Price = 400 AND

  Prod_Finish = ‘Ash’ OR Prod_Finish = ‘Red

  
• give the id of the answers = 3,5

  SELECT *

  FROM Product_T

  WHERE Standard_Price = 400 AND

  (Prod_Finish = ‘Ash’ OR Prod_Finish = ‘Red)

  
• Display all fields for those whose finish is red and price is greater than

  400 or those whose finish is cherry and price is less than 400.

  \

  SELECT *

  FROM table

  WHERE finish='red' AND price>400 OR

  finish='cherry' AND PRICE<400;

  Display prod id and description of those whose finish is either red or

  cherry and whose price is between 400 and 500 inclusive

  \

  SELECT prod id, description

  FROM table

  WHERE (finish='red' OR finish='cherry') AND price>=400 AND price<=500
• The WHERE clause may include the IN keyword to specify that a particular column value must match one of the values in a list

  SELECT EMP_NAME

  FROM EMP_T

  WHERE DEPTID IN (5, 8, 10);

  \

  same as

  \

  SELECT EMP_NAME

  FROM EMP_T

  WHERE DEPTID = 5 OR DEPTID = 8 OR DEPTID = 10;
• Any criteria statements can be preceded by a NOT operator in order to invert the results

  ◦Using NOT will return all information except the information matching the specified criteria

  SELECT EMP_NAME

  FROM EMP_T

  WHERE DEPTID NOT IN (5, 8, 10);
• answer = 4,2kunwari name at state yan)

  SELECT Cust_name, State

  FROM Customer_t

  WHERE City NOT IN (‘QC’, ‘Taguig’)

  ORDER BY State;

  
• SQL provides a BETWEEN keyword that allows a user to

  specify a minimum and maximum value on one line

  ◦BETWEEN is inclusive

  SELECT EMP_NAME

  FROM EMP_T

  WHERE SALARY >=1000 AND SALARY <= 5000;

  \same as

  SELECT EMP_NAME

  FROM EMP_T

  WHERE SALARY BETWEEN 1000 AND 5000;
• The SQL LIKE keyword allows for searches on partial data values

  LIKE can be paired with wildcards to find rows that partially match a string value

  ◦The multiple character wildcard is an percen sign (%)

  ◦The single character wildcard character is a underscore(_)

  Examples:

  SELECT emp_name

  FROM Emp_T

  WHERE joblevel LIKE ‘S%’;

  SELECT emp_name

  FROM emp_t

  WHERE Phone_T LIKE ‘632-_ _ _ _ _ _ _’