Data Definition Language

CREATE TABLE Book (
	ISBN  CHAR(9)     PRIMARY KEY,
	Title VARCHAR(20) UNIQUE NOT NULL,
	Pages Integer
);

CREATE TABLE Book (
	ISBN  CHAR(9)     ,
	Title VARCHAR(20) NOT NULL,
	Pages Integer,
	CONSTRAINT Book_PK PRIMARY KEY(Isbn),
	CONSTRAINT Book_title UNIQUE (Title)
);

CREATE TABLE Person (
	Id     INTEGER     PRIMARY KEY,
	SSN    CHAR(9)     UNIQUE,
	Name   VARCHAR(20) NOT NULL,
	Age    INTEGER     DEFAULT 18 
                         CHECK (Age BETWEEN 10 AND 100),
	Major  CHAR(3) REFERENCES Major(Id)
);

CREATE TABLE Standing (
  deg_code      char(2) REFERENCES Degree(deg_code),
  min_cr        INTEGER DEFAULT 0 NOT NULL,
  max_cr        INTEGER NOT NULL,
  num           INTEGER NOT NULL,
  designation   VARCHAR(20) NOT NULL,
  CONSTRAINT Standing_PK
        PRIMARY KEY (deg_code, num),
  CONSTRAINT Standing_Unique_Designation
        UNIQUE (deg_code, designation),
  CONSTRAINT Standing_min_max
        CHECK (min_cr <= max_cr)
);

Data Manipulation Language (DML)

SQL's DML includes statements to do alter the rows in a table, and to get data from one or more tables. Notice that this statements do NOT alter the schema at all, only the data in the table. The main statements are:

Since the SELECT statement is more complex, we devote a separate section to it, after covering the other DML statements.

INSERT INTO tableName (field1,field2,...) VALUES (value1, v2,...)

INSERT INTO Person (Id,SSN,Name,Age,Major) VALUES (1, '11111','Orlando',30,'CS')

DELETE FROM Person WHERE Age < 20

UPDATE Person
SET Age=Age+1;

UPDATE Person
SET Name='Orlando', Age=35
WHERE Id=1;

The SELECT Statement

The SELECT statement allows you to get data from one or more tables. It does NOT change the data stored in any way, it just allows you to read it. The SELECT Statement is probably the most complex SQL statement, since it needs to allow you for complex conditions and to get data from several tables.

In its basic form, the SELECT statement contains three clauses, although the WHERE clause is actually optional. After the SELECT keyword, we specify the fields (or expressions) we want to retrieve; then we add the FROM keyword and the table from which we are getting the data, and finally a WHERE clause that specifies which rows are to be returned.

The simplest select statement uses * instead of a list of attributes (retrieving ALL columns from the table) and does not use a WHERE clause (retrieving ALL rows), so the statement to list all data in the person table would be:

SELECT *
FROM Person

We can also specify a list of fields, or even expressions. When using expressions, it is often convenient to give the expression a nice name, so SQL allows us to alias a field or expression, by writing the keyword AS and then alias after the field name or expression (actually, the AS keyword is optional in most DBMSs, although PostgreSQL requires it).

So the following would be a valid example:

SELECT Id AS Identifier, Name, Age+5 AS AgeIn5 FROM Person

and it would return data as follows:

Notice that the last field retrieved uses an expression, Age+5. Most DBMSs support the standard math operators, many of the basic math functions (floor, sin, cos etc), and many string functions (upper, lower, substr etc); consult your DBMS's manual.

SQL statements become slightly more interesting when we add conditions, which allow us to retrieve only certain rows, rather than the whole table. We can add conditions with the WHERE clause, and can use the traditional comparison operators (<, <= etc) and combine them with the traditional logical operators (AND, OR, NOT). Differently from most programming languages, SQL uses the actual words AND OR and NOT for those operators. The precedence of the operators is the usual (NOT, then AND, finally OR), and you can use parenthesis to alter that precedence.

So, a query that would return only females who are older than 20 would be:

SELECT *FROM Person
WHERE Gender='F' AND Age>20

SELECT COUNT(*)
FROM Person;

SELECT COUNT(*)
FROM Person
WHERE Gender='F';

SELECT Country, COUNT(*) as NumPeople, AVG(Age)
FROM Person 
GROUP BY Country;

SELECT Country, COUNT(*) as NumPeople, AVG(Age)
FROM Person
GROUP BY Country
HAVING COUNT(*) >= 1

JOINS

Up to now, we have only seen how to get results from a single table; but we oftentimes need to combine information from several tables; in fact, good relational design requires us to divide our information into many tables, and to get useful information we need to combine those tables.

The main operation to combine information from several tables is the join operation. We can think of a join as a combination of a cartesian product of two tables (which gets us all pairs of rows, with one coming from each table) followed by a selection process (which allows us to get only those pairs that 'match' according to some predicate).

SQL supports several ways to do joins. The simplest way, which I call an implicit join is to put two or more tables in the FROM clause, separated by commas. The join condition (that is, the predicate that decides which rows match) is added to the WHERE clause.

For example, assume we have another table, called country, that gives us the name of the country given their iso code (we have an extra country that we'll use later):

So, if we want to get the names of all people with the name of the country they were born in, we can use the following query:

SELECT Person.Name, Country.Name
FROM Person, Country
WHERE Person.Country=Country.Code

Notice how we need to use Person.Name and Country.Name in the first line, to disambiguate to which field we are referring to. For clarity, we also do it on the third line, but it is not necessary there (since there are no two fields called Country or Code). To save on typing but keep the clarity, I usually alias the tables to their initial letter, so the query looks like this:

SELECT P.Name, C.Name
FROM Person P, Country C
WHERE P.Country=C.Code

For a simple query like this, an implicit join is appropriate; however, when you are joining several tables, and have many conditions, some arising from the join and others from other requirements, the implicit join syntax can easily lead to mistakes.

SQL provides another syntax for join statements, which can make the intent easier. Since this syntax includes the JOIN keyword, I call this an explicit join statement. The syntax involves writing the table names, with the keyword JOIN between them, followed by the keyword ON and a condition between parenthesis; for example,

SELECT Person.Name, Country.NameFROM Person JOIN Country ON (Person.Country=Country.Code)Example 19: Retrieve names of all people with their country's name

SELECT *
FROM Person NATURAL JOIN Country 

SELECT *
FROM Person JOIN Country USING (Name)

SELECT P.Name, C.NameFROM Person P LEFT OUTER JOIN Country C ON (P.Country=C.Code)

Subqueries

In most places where we use a table name or an expression that returns a list, SQL allows us to substitute another SQL query (a subquery). We can use subqueries in FROM clauses, or instead of a list, for operators that accept a list (IN, EXISTS, =ANY etc)

For example, imagine that we add a new table, HasLived, that reflects the fact that a person has lived in a country. The table with sample data could look like Table x

From this and the previous tables, we can see that Orlando (with id 1) has lived in Mexico and the USA, that Lina has lived in Mexico etc

Now if we are asked to find the names of people who have lived in the country with id 'us' (namely the USA); the query would look like:

SELECT P.Name
FROM Person P JOIN HasLived Hl ON (P.Id=Hl.Person)
WHERE Hl.country = 'us'

But imagine that we are asked for the names of people who have NOT lived in the 'us'; we could be tempted to modify the condition in the WHERE clause above, as follows:

SELECT P.Name
FROM Person P JOIN HasLived Hl ON (P.Id=Hl.Person)
WHERE Hl.country != 'us'

This query actually returns the names of people who've lived somewhere other than in 'us'The problem is that this query actually asks for the people who have lived in a country other than 'us', which, given that there may be more that one row per person (i.e. a person may have lived in more than one place), it is NOT an equivalent query.

In our example, the only person who has not lived in 'us' is Lina (person with id 2); but the above query would also return the people with id 1 and 4, since they've lived in other countries.

The right solution would look like:

SELECT P.Name
FROM Person P WHERE Id NOT IN (
    SELECT Person
    FROM HasLived
    WHERE Country='us'
)

Here the inner query selects the id's of people who have lived in 'us' and the main query selects all people except those.

As another example, say we're asked to find people who have lived in both the country with id 'mx' and the one with id 'us'; again a naïve (and wrong) attempt would be:

SELECT P.Name
FROM Person P JOIN HasLived Hl ON (P.Id=Hl.Person)
WHERE Hl.country = 'us' AND Hl.country='mx'

This query would actually return no rows at all. The condition in the where clause, is tested for the same row, so we are actually requiring that the same country field be 'us' and 'mx', which is obviously impossible, since a field can only have one value for a given row.

The correct query would use IN, as follows:

SELECT P.NameFROM Person P WHERE Id IN (
    SELECT PersonFROM HasLivedWHERE Country='us') 
AND Id IN (
    SELECT PersonFROM HasLivedWHERE Country='mx');