Month: February 2020

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.