bellalee

SQL

2 min read

SQL is a Structured Query Language that is domain-specfic, meaning it only works in relational databases. It allows for a logical level of interaction with the data in a database. As a declarative query language, it focuses on retrieving the information needed, and not on how it is retrieved. Java and Python are examples of procedural query languages.

SQL is case-insensitive and not evaluated line by line (see example below). It does not pay attention to line breaks, and strings are free unless there are quotations mark. It is best to use consistent guidelines for readability.

terms & definitions

  • Foreign key: one or more attributes that uniquely identifies a row in another table

basic query example

SELECT P.Name, P.UserID
FROM Payroll AS P
WHERE P.Job = 'TA';
  • Scans for alias first

basic commands

  • *: select all
  • ORDER BY: orders result tuples by specified attributes (default asc)
    • Inverse: ORDER BY P.Salary DESC
  • DISTINCT: deduplicates result tuples
    • e.g. selecting job where salary is greater than 70k

creating tables

CREATE TABLE Payroll (
	UserID INT PRIMARY KEY,
	Name VARCHAR(100),
	Job VARCHAR(100),
	SALARY INT);

OR

CREATE TABLE Payroll (
	UserID INT,
	Name VARCHAR(100),
	Job VARCHAR(100),
	SALARY INT
	PRIMARY KEY (UserID)
);
  • Can use second option to set multi-attribute keys if no single attribute is unique (e.g. PRIMARY KEY (Name, Job))
  • Key: one or more attributes that uniquely identify a row

data types

  • Each attribute has a type
  • Statically and strictly enforced
  • List
    • VARCHAR(N) for strings where N is max character length
      • Set as large as you need
    • INT, FLOAT for numbers
    • Strings: CHAR(N), VARCHAR(N), TEXT
    • Numbers: INT, SMALLINT, FLOAT
    • MONEY, DATETIME
    • A few more DBMS specific types

foreign keys

  CREATE TABLE Regist (
	  UserID INT REFERENCES Payroll(UserID),
	  Car VARCHAR(100)
  );

OR

CREATE TABLE Regist (
	UserID INT,
	NAME VARCHAR(100),
	Car VARCHAR(100),
	FOREIGN KEY (UserID, Name)
		REFERENCES Payroll
);

database internal

SQL is passed into the parser, which uses relational algebra (RA) language to boil the code down to instructions. It uses for-each semantics for queries.

references

Graph View

Recent Notes