ITM 111: Introduction to Databases

W02 Quiz: SQL Overview.

What is SQL

SQL, or Structured Query Language, is a specialized programming language designed for managing, manipulating, and retrieving data stored in relational databases. It serves as a universal language for communicating with databases, enabling users to perform various operations such as querying data, inserting new records, updating existing records, and deleting unwanted data. SQL provides a standardized syntax and set of commands that allow users to interact with databases regardless of the specific database management system (DBMS) being used, making it an essential tool for developers, data analysts, and database administrators. Its versatility and widespread adoption make SQL a fundamental skill in the field of data management and analytics.

Syntax vs. Semantics

Each SQL Statement has syntactic and semantic component. In SQL, the distinction between syntax and semantics is crucial for understanding and effectively using the language.

Syntax refers to the rules governing the structure and grammar of SQL statements, determining whether a query is written correctly according to the language's conventions. It encompasses aspects such as the placement of keywords, the use of punctuation, and the order of clauses within a statement. Syntax errors arise when a query violates these rules, often resulting in the query being rejected by the database management system without execution.

Semantics pertain to the meaning and interpretation of SQL statements, determining the behavior of queries and their effects on the database. Semantics encompass concepts such as data retrieval, manipulation, transaction management, and integrity constraints, ensuring that queries are executed correctly and produce the desired results. While syntax errors can be detected by the SQL parser, semantic errors—such as querying for data that doesn't exist or unintentionally modifying data—require a deeper understanding of SQL's functionality and the underlying database schema to identify and rectify effectively.

SQL Statements

A SQL statement is a command used to perform a specific action on a database. It is written in the Structured Query Language (SQL) its usage ranges from querying data to modifying the database structure. SQL statements can be categorized into several types, including Data Manipulation Language (DML) statements like SELECT, INSERT, UPDATE, and DELETE, which are used to retrieve, add, modify, and remove data from tables respectively. Additionally, there are Data Definition Language (DDL) statements like CREATE, ALTER, and DROP, which are used to define, modify, and delete database objects such as tables, indexes, and views.

Parts of a SQL Statement

SQL statements are made up of several parts. The basic parts are:

• Clause: A clause is a component of a SQL statement that specifies a particular action to be performed. Common clauses include SELECT, INSERT, UPDATE, DELETE, WHERE, GROUP BY, HAVING, ORDER BY, JOIN, CREATE, ALTER and DROP.
• Keywords: Keywords are reserved words in SQL that have special meanings. These include commands like SELECT, INSERT, UPDATE, DELETE, WHERE, FROM, JOIN, and others. Keywords are used to define the type of operation to be performed or to specify conditions.
• Expressions: Expressions are combinations of literals (and exact number, or text), column names, operators, and functions that evaluate to a single value. They can be used to define calculations, conditions, or values to be returned in a query.
• Identifiers: Identifiers are names given to database objects such as tables, columns, indexes, and constraints. They must adhere to certain rules depending on the database system used, such as being enclosed in quotes or adhering to specific naming conventions.
• Operators: Operators are symbols or keywords used to perform comparisons or operations in SQL statements. Common operators include arithmetic operators

A note on case sensitivity: The SQL language itself is not case sensitive, however depending on the database management system (DBMS) identifiers may be. Most DBMSs do not have case sensitive identifiers, however PostgreSQL uses case sensitive identifiers by default. Oracle, Microsoft SLQ Server, and MySQL do not use case sensitive identifiers by default. Keywords and text comparisons are not case sensitive in most DBMSs. Know the rules of your DMBS before you code.

Example Statement

Let's look at the SQL statement

SELECT name, age FROM student WHERE student_id = 2983756;

The previous statement consist of

• Keywords: SELECT, FROM, WHERE
• Identifiers: name, age, student, student_id
• Operator: =
• Number literal: 2983756 (note: text literals must be enclosed in quotes e.g. 'this is a text literal')
• ; (semicolon): Statement terminator

How it works

• SELECT: This keyword is used to retrieve data from a database. In this statement, it's telling the database to retrieve specific columns.
• name, age: These identifiers are the column names that you want to retrieve from the database. In this case, you want to retrieve the "name" and "age" columns.
• FROM student: This specifies the table from which you want to retrieve the data. In this statement, it's indicating that you want to retrieve data from a table called "student".
• WHERE student_id = 2983756: This is a condition that filters the rows returned by the query. It specifies that you only want the rows where the "student_id" column equals the value 2983756.

SQL Clauses

Some keywords start a portion of the statement called a clause. Each clause performs a specific kind of action. Clauses must be in the correct order. Each SQL statement does not need to contain all clauses. Some common clauses (in order) are:

1. SELECT: The SELECT clause specifies the columns you want to retrieve from the database. For example, SELECT name, age would retrieve the "name" and "age" columns from a table.
2. FROM: The FROM clause specifies the table from which you want to retrieve data. For instance, FROM students indicates that you're retrieving data from a table named "students".
3. WHERE: The WHERE clause filters rows based on specific conditions. It allows you to retrieve only the rows that meet the criteria you specify. For example, WHERE age > 18 would retrieve only the rows where the "age" column is greater than 18.
4. GROUP BY: The GROUP BY clause is used with aggregate functions (like SUM, COUNT, AVG) to group rows that have the same values into summary rows. It's often used in conjunction with aggregate functions to perform calculations on groups of data.
5. HAVING: The HAVING clause is similar to the WHERE clause but is used specifically with the GROUP BY clause. It filters groups based on specified conditions. For instance, HAVING COUNT(*) > 10 would filter groups to only those with more than 10 rows.
6. ORDER BY: The ORDER BY clause is used to sort the result set based on specified columns, either in ascending (ASC) or descending (DESC) order. For example, ORDER BY name DESC would sort the result set by the "name" column in descending order.
7. LIMIT or OFFSET: These clauses are used to limit the number of rows returned by the query, or to skip a certain number of rows.

You could write the previous example like this. Notice this format indicates the clauses and makes the SQL statement easier to read. You may notice that a single SQL statement can be spread across multiple lines. The statement is ended with a semicolon.

SELECT 
  name, age 
FROM 
  student 
WHERE student_id = 2983756;

Here are a few more statements

SELECT * FROM teacher;

Returns all of the records from the teacher table. The * is used instead of a column name to indicate all columns should be returned.

SELECT name FROM department WHERE code='ITM';

Returns the value in the name column from the department table if the code column is equal to the text ITM

Operators

SQL operators are symbols or keywords used to perform comparisons, arithmetic operations, logical operations, and string operations within SQL statements. Here's a list of common SQL operators:

• Arithmetic Operators:
  • + (addition)
  • - (Subtraction)
  • * (Multiplication)
  • / (Division)
  • % (Modulus)
• Comparison Operators:
  • = (Equal to)
  • <> (Not equal to)
  • < (Less than)
  • > (Greater than)
  • <= (Less than or equal to)
  • >= (Greater than or equal to)
• Logical Operators:
  • AND (Logical AND)
  • OR (Logical OR)
  • NOT (Logical NOT)
• NULL Comparison Operators:
  • IS NULL (Checks if a value is NULL)
  • IS NOT NULL (Checks if a value is not NULL)
• Pattern Matching Operators (used with LIKE in many SQL dialects):
  • % (Matches zero or more characters)
  • _ (Matches exactly one character)

By combining the SQL clauses with the appropriate operators you can retrieve whatever data you want from the database and display or modify it in any way you desire.

Data Definition Language (DDL)

Many SQL commands manipulate the actual structure of your database. CREATE, ALTER and DROP are keywords that allow you to change the structure of a database. Each of the keywords takes an additional keyword to identify what type of database object to work with. Examples are DATABASE, TABLE, and INDEX.

CREATE

CREATE is used to add new objects to the database or even a new database. The following example creates a new database named demo

CREATE DATABASE demo;

Another useful keyword is USE, USE instructs the DBMS to set a specific database as the default database if the SQL command does not specify a database.

USE demo;

CREATE can also be used to add a new table to the database. The following command adds a new table with several rows (don't worry about all the details you will learn those as you work through the course).

CREATE TABLE user
  (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(25) NOT NULL,
      last_name VARCHAR(25) NOT NULL,
      birth_year INTEGER NULL,
      birth_city VARCHAR(25),
      ssn char(9)
  );

DROP

The DROP keyword is used to remove an object from the database or even delete the whole database. Be careful!. Here are a couple of examples, they are pretty self explanatory.

DROP DATABASE demo;
DROP TABLE user;

ALTER

The ALTER keyword is used to modify an object in the database. The following statement will remove the ssn column from the user table. Notice it also uses the DROP keyword.

ALTER TABLE user DROP COLUMN ssn;

Don't spent too much time on this commands as a beginner, in our course most of the Object creation and manipulation will be done with MySQL Workbench which allows you to create database designs in a graphical environment, then the tool creates the SQL statements to run.

CRUD with Data Manipulation Language DML

This class will focus more on the DML side of SQL, you will explore these statements in great detail throughout the semester. This introduction will provide a bit of a primer to get you started. By far the most often repeated tasks of using a database are the Create, Read, Update, and Delete, fondly known as CRUD. Before I introduce you to the statements you must understand the difference between queries that return rows of data, and queries that do not return rows of data. Nearly all SQL statements are non row returning queries, in other works they do not return data as a set of records. These queries typically return a single number that represents that status of query of times it is the number of rows affected. Of the statements in this document, only the SELECT statement returns rows of data.

INSERT (Create)

The insert statement is used to add data to a table it takes the generic form of;

INSERT INTO table_name (column1, column2, column3, ...)
  VALUES (value1, value2, value3, ...);

The values are inserted into the columns in the order the columns are listed in the column list. Here are some things to keep in mind

• The list of columns and the list of values must have the same number of elements.
• If you omit the list of columns there must be a value for each column in the table.
• You can add multiple rows of data at once by adding additional lists of values, each list of values must be enclosed in parenthesis and separated by a comma.
• If you use NULL for a value, the database will insert the default value for the column if it has one. Autogenerated columns will have their value generated.

Here are a few examples of inserting data into the table we defined earlier with this statement:

-- Create sample user table
  CREATE TABLE user
  (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(25) NOT NULL,
      last_name VARCHAR(25) NOT NULL,
      birth_year INTEGER NULL,
      birth_city VARCHAR(25),
      ssn char(9)
  );
-- insert without id column since it is autogenerated
INSERT INTO user 
  (first_name, last_name, birth_year, birth_city, ssn)
VALUES 
  ('Marco', 'Guadalupe', 1965, 'San Antonio','598-27-1689');
-- insert without listing columns to insert into
INSERT INTO user 
VALUES 
  (NULL, 'Alecia', 'McMaster',1975, 'San Diego','187-81-9516');
-- insert multiple users at once none have ssn
INSERT INTO user 
  (first_name, last_name, birth_year, birth_city)
VALUES 
  ('Maria', 'Santos',1985, 'Manilla'),
    ('Juan ', 'Dela Cruz',1975, 'Manilla'),
    ('Clara ', 'Reyes',1988, 'Manilla');
    

Select (Read)

The SELECT keyword is used to retrieve data from a table or series of tables, you can choose which columns to return and filter the returned data by using a WHERE clause. Here are some examples using the above inserted data.

-- return all data from the user table
SELECT * FROM user;
    

-- return all data where the birth city is manilla
SELECT
  * 
FROM
  user
WHERE birth_city='manilla';
    

-- return just the last and first names sorted by last name
SELECT 
  Last_name, first_name 
FROM 
  user
ORDER BY 
  last_name;
    

-- create a calculated column that represents the year the user turns 50    
SELECT 
  last_name, first_name, birth_year + 50 AS turns_50_in
FROM 
  user;
    

-- select the users who's birth_city starts with san    
SELECT
  * 
FROM
  user
WHERE birth_city LIKE 'san%';
    

UPDATE (Update)

The UPDATE keyword allows you to change data in a table. You can change any number of columns and any rows you can filter on. The basic form of the UPDATE statement is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
      

If you omit the WHERE clause the changes will be made to every row in the table!

-- Change maria's name to martha
UPDATE
  user
SET 
  first_name = 'Martha'
WHERE
  id=3;
    

Delete (Delete)

The Delete keyword allows you to remove data from a table. All rows that meet the WHERE condition will be deleted!. The basic form of the UPDATE statement is as follows:

DELETE FROM table_name
WHERE condition;
        

If you omit the WHERE clause ALL rows will be deleted from the table!

-- Delete all users from Manilla    
DELETE FROM
  user
WHERE 
  birth_city ='manilla';
        

Summary

Wow, that was a lot of information, don't worry you will spend the rest of the semester working on it. This is just a quick overview to help you become familiar with the words you will be using for the rest of the semester. The basics of SQL are pretty simple and straight forward, however it is an amazingly powerful language to work with data.

Submission

Using this overview and the datawinter database as a reference, answer the W02 Assignment: SQL Overview questions in Canvas

Useful Links:

• Go to: Week Index • Course Home • Canvas