Adv Database Management: SQL Sub-queries and views

Please note that the following blog post provides a summary view for what you need to get done (left column) and quick examples that illustrate how to do it in SQL (right column with SQL code in red). For more information please see the resources below:

Subquery
SELECT enames
  FROM emp
  WHERE sal >
    (SELECT sal
     FROM emp
     WHERE empno = 7566)
Correlated Subqueries
SELECT empno, sal, deptno
  FROM   emp outr
  WHERE  sal >
    (SELECT AVG(sal)
     FROM   emp innr
     WHERE  outr.deptno = innr.deptno)
Exists
SELECT empno, ename, job, deptno
  FROM   emp outr
  WHERE  EXISTS
    (SELECT empno
     FROM   emp innr
     WHERE  innr.mgr = outr.empno)
Not Exists
SELECT dname, deptno
  FROM   dept d
  WHERE  NOT EXISTS
    (SELECT *
     FROM   emp e
     WHERE  d.deptno = e.deptno)
In
SELECT empno, ename, job, deptno
  FROM   emp outr
  WHERE empno IN
    (SELECT mgr
     FROM   emp)

Creating a view
CREATE VIEW empvu10
  AS SELECT empno, ename, job
     FROM emp
     WHERE deptno = 10
Drop view
DROP VIEW empvu10

Adv Database Management: SQL Group functions

Please note that the following blog post provides a summary view for what you need to get done (left column) and quick examples that illustrate how to do it in SQL (right column with SQL code in red). For more information please see the resources below:

AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE
SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)
  FROM emp
  WHERE jobs LIKE ‘Sales%’
COUNT
SELECT COUNT(*)
  FROM emp
  WHERE deptno = 30
Group By
SELECT deptno, AVG(sal)
  FROM emp
  GROUP BY deptno
Rollup and cube
SELECT   deptno, MAX(sal)
  FROM     emp
  GROUP BY deptno WITH ROLLUP [CUBE]
Having
SELECT   deptno, MAX(sal)
  FROM     emp
  GROUP BY deptno
  HAVING max(sal)>2900

 

Database Management: SQL Joins

Please note that the following blog post provides a summary view for what you need to get done (left column) and quick examples that illustrate how to do it in SQL (right column with SQL code in red). For more information please see the resources below:

Equijoins
SELECT e.ename, e.deptno,  d.deptno, d.name
  FROM emp e INNER JOIN dept d
  ON e.deptno = d.deptno
Non-Equijoins
SELECT e.ename, e.sal,  s.grade
  FROM emp e INNER JOIN salgrade s
  WHERE e.sal
  BETWEEN  s.losal  AND  s.hisal

From:
grade      losal        hisal
-----      -----        ------
1            700        1200
2           1201        1400
3           1401        2000
4           2001        3000
5           3001        9999

Gives the following solution:
ename           sal     grade
----------   --------- ---------
JAMES            950         1
SMITH            800         1
ADAMS           1100         1
Outer joins
SELECT e.ename, e.deptno,  d.deptno
  FROM emp e RIGHT JOIN dept d
  ON e.deptno = d.deptno

SELECT e.deptno,  d.deptno, d.name
  FROM emp e LEFT JOIN dept d
  ON e.deptno = d.deptno
Self Joins
SELECT worker.ename +’ works for’+ manager.ename
  FROM emp worker, emp manger
  ON worker.mgr = manager.empno

 

Database Management: SQL Select

Please note that the following blog post provides a summary view for what you need to get done (left column) and quick examples that illustrate how to do it in SQL (right column with SQL code in red). For more information please see the resources below:

Select
SELECT *
  FROM dept
Parentheses
SELECT ename, sal, 12*(sal+100)
  FROM emp
Column Alias
SELECT ename, AS name, sal salary
  FROM emp
Eliminating Duplicate Rows
SELECT DISTINCT deptno
  FROM emp
Selecting with WHERE
SELECT ename, job, deptno
  FROM emp
  WHERE job=’Clerk’
Comparison Operators in WHERE
SELECT ename, sal, comm
  FROM emp
  WHERE sal<=comm
Between Operators in WHERE
SELECT ename, sal, comm
  FROM emp
  WHERE sal BETWEEN 1000 AND 1500
Test Values in a list
SELECT ename, empno, mgr
  FROM emp
  WHERE mgr IN (7902, 7566, 7788)
Pattern Matching in WHERE

_ is one space character *

% is trailing characters *

* is all characters

SELECT ename
  FROM emp
  WHERE ename LIKE ‘_A%’
Both conditions in WHERE are true
SELECT ename, empno, job, sal
  FROM emp
  WHERE sal>=1100
  AND job=’Clerk’
One or the other in WHERE are true
SELECT ename, empno, job, sal
  FROM emp
  WHERE sal>=1100
  OR job=’Clerk’
Not Operator in WHERE
SELECT ename, empno, job, sal
  FROM emp
  WHERE job NOT IN (’Clerk’, ’Manager’, ’Analyst,)
Sort rows
SELECT ename, job, deptno, hiredate
  FROM emp
  ORDER BY hiredate
Descending order sorting of rows
SELECT ename, job, deptno, hiredate
  FROM emp
  ORDER BY hiredate DESC
Table aliases
SELECT e.empno, e.ename, e.deptno,  d.deptno, d.loc
  FROM emp e, dept d
  WHERE e.deptno = d.deptno

 

Database Management: SQL Basics

Please note that the following blog post provides a summary view for what you need to get done (left column) and quick examples that illustrate how to do it in SQL (right column with SQL code in red). For more information please see the resources below:

Create table
CREATE TABLE dept
  (deptno NUMERIC(2),
   dname VARCHAR(14),
   loc VARCHAR(14))
Add Column
ALTER TABLE dept
  ADD job VARCHAR (9);
Rename Table
RENAME dept TO department
Rename Column
ALTER TABLE dept ALTER COLUMN job RENAME TO career
Delete Table
DROP TABLE dept
Not Null in a table (forcing there to be a value in that variable in the table)
CREATE TABLE dept
  (deptno NUMERIC(2) NOT NULL,
   dname VARCHAR(14),
   loc VARCHAR(14))
Unique (allows for null values, but doesn’t allow for the same variable to be repeated)
CREATE TABLE dept
  (deptno NUMERIC(2),
   dname VARCHAR(14),
   loc VARCHAR(14),
   CONSTRAINT dept_dname_uk UNIQUE (dname))
Primary Key (doesn’t allow for null values)
CREATE TABLE dept
  (deptno NUMERIC(2),
    dname VARCHAR(14),
    loc VARCHAR(14),
    CONSTRAINT dept_dname_uk UNIQUE (dname),
    CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno))
Foreign Keys (connect data with other tables)
CREATE TABLE emp
  (empno NUMERIC(4),
   ename VARCHAR(10) NOT NULL,
   job VARCHAR(9),
   mgr NUMERIC(4),
   hiredate DATETIME,
   sal MONEY,
   comm MONEY,
   deptno NUMERIC(2) NOT NULL,
   CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept (deptno))
)
Check Constraints (sanity checks)
CREATE TABLE Dept
  (deptno NUMERIC(2),
    dname VARCHAR(14),
    loc VARCHAR(14),
    CONSTRAINT dept_dname_uk UNIQUE (dname),
    CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno),
    CONSTRAINT emp_deptno_ck CHECK (deptno BETWEEN 10 AND 99))
Insert new rows of data
INSERT INTO dept (deptno, dname, loc)
  VALUES (50, ‘development’, ‘Detroit’)
Change data within the table
UPDATE emp
  SET deptno = 20
  WHERE empno = 7782
Removing a row form a table
DELETE FROM dept
  WHERE dname = ‘development’
Removing data entry in all tables (CAUTION)
DELETE CASCADE FROM dept
  WHERE dname = ‘development’

NOTE:

  • When doing delete stuff, do a SELECT statement with the WHERE clause to make sure you are not shooting in the dark.

 

Sample SQL sets: Querying Tables

Below is an ERD of a database supporting the basic revenue business cycle.

Capture

Important: Use only the information that is given in the request in creating your queries.

Queries Examples:

  1. Provide a list of the descriptions and list prices of all the products the company
Select ProdDesc Description, ListPrice Prices
     From Product
  1. What products does the company have at least 10 items in stock?
Select ProdDesc Description, QuantityOnHand AS 'Quantity Available'
     From Product
          Where QuantityOnHand >= 10
  1. Sort all of the product descriptions in alphabetical order.
Select ProdDesc Description
     From Product
          Order by ProdDesc
  1. What are the names of the customers who live in Oklahoma or Arizona?
Select LName + ', ' + FName AS Name, State
     From Customer
          Where State = 'OK'
          or State = 'AZ'
  1. How many items on hand does the company have for the following product ids: (1, 4, 9, 10, 13, 22)?
Select ProductID, ProdDesc Description, QuantityOnHand AS 'Quantity Available'
     From Product
          Where ProductID IN (1,4,9,10,13,22)
  1. Which customers made orders on August 23, 2008 (no redundancy in results)?
Select Distinct CustomerID, OrderDate
     From Orders
          Where OrderDate='August 23, 2008'
  1. Which customers have ordered printers (no redundancy in results)?
Select Distinct o.CustomerID, p.ProdDesc
     From Orderline ol, Product p, orders o
          Where o.OrderID = ol.OrderID
          and ol.ProductID = p.ProductID
          and ProdDesc='printer'

8. Table Creation

Create a table called BackOrder. BackOrder should have the following columns: BO_ID, DelayDesc, Date, and ProductID.

When you create BackOrder, you must specify a PK constraint for BO_ID and an FK constraint for ProductID. DelayDesc should be a VarChar data type and can be null. The date should be a date data type and cannot be null.

Create Table BackOrder
(
       BO_ID Numeric(4),
       DelayDesc Varchar(15),
       Date Datetime Not Null,
       ProductID Numeric(18) Not Null,
       Constraint BackOrder_hern5717_BO_ID_pk Primary Key (BO_ID),
       Constraint Product_productID_fk Foreign Key (ProductID) References product(ProductID)
)

Insert a few lines into the table, then delete the lines.

Insert into BackOrder (BO_ID, DelayDesc, Date, ProductID)
   values (1,'Oklahoma Constitution', '1/14/2014', 1),
    (2,'Utah Constitution', '12/23/2013', 1),
    (3,'New Mexico Constitution', '12/21/2013', 1)

Delete from BackOrder
       Where BO_ID = 2