Introduction What is a Relational Database? A relational database is a collection of data stored in tables. Each table consists of one or more vertical columns and zero or more horizontal rows. Tables are identified by name Table names must be unique within a database. Typical SQL databases allow up to 18 characters in a table name. The first character must be a letter, while the remaining characters can be letters, numbers, or underscores. ...
A SQL statement is a collection of clauses, keywords, and parameters that perform a particular function. In the examples below, each clause is shown on a separate line; keywords appear in all uppercase letters, parameters in all lowercase letters. Clauses The various clauses of SQL statements are named after their initial words: the SELECT clause, for example, or the FROM clause. SQL allows multiple clauses per line, but most SQL programmers use separate lines for clarity and ease of editing. ...
The LIKE Operator The LIKE operator is used to find values that match a pattern. Patterns are always entered in quotes. A per cent symbol is used to represent zero or more unknown characters; an underscore represents any single character. Note: The LIKE operator is added to the WHERE clause, as shown in the examples below. Filters rows where the “user” column starts with “Abd” followed by any characters. SELECT * FROM angaza_users WHERE user LIKE 'Abd%'; It’s looking for values in the “user” column that start with “De” and end with “ni,” with any characters in between. ...
Arithmetic Expressions An arithmetic expression is a phrase formed with operands (numeric values and/or column names) and arithmetic operators (shown below). Arithmetic expressions are evaluated by SQL and replaced with the appropriate numeric value. Operands can be numeric values or column names In an expression, valid operands include numbers, column names, and other expressions. When a column is used in an expression, the values in that column must be numeric or valid dates. Only addition and subtraction are valid with dates. ...
Aggregation Functions An aggregation (statistical) function is a built-in program that accepts a parameter and returns a summary value. The parameter may be either a column name or an expression. The five statistical functions supported by standard SQL are shown in the following table. Statistical functions accept parameters Parameters are either column names or expressions. Parameters must always be enclosed in parentheses. Note that spaces inside and outside the parentheses are optional but are shown in the examples above for readability. ...
Joins The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means of merging two tables based on common fields. INNER JOIN: returns rows when there is a match in both tables. LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table. RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table. FULL JOIN: returns rows when there is a match in one of the tables. Note: FULL JOIN cannot be implemented directly in MySQL dialet. In this dialect, FULL JOIN is the UNION between RIGHT JOIN and LEFT JOIN. ...
Single-Valued Subqueries A single-valued subquery is a query that 1) produces a result with a single column and a single row and 2) is ’nested’ in the WHERE clause of another query. Subqueries must be enclosed in parentheses. A single-valued queries: SELECT AVG(days_to_cutoff) FROM accounts; Sing-valued sub-query: SELECT * FROM accounts WHERE days_to_cutoff > (SELECT AVG(days_to_cutoff) FROM accounts) ORDER BY days_to_cutoff; Key Points: Single-valued queries can be used in comparisons Subqueries are enclosed in parentheses Multi-Valued Subqueries A multi-valued subquery is a query that 1) returns a single-column result with zero, one, or more rows and 2) is ’nested’ in the WHERE clause of another query. Multi-valued subqueries always follow the IN operator. ...
Creating Tables in SQL New tables are defined in a database by entering a CREATE TABLE statement. In its simplest form, the CREATE TABLE statement includes a new table name and one or more column definitions. Tables are removed using the DROP TABLE statement. SQL Syntax for Creating Tables: CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, ... ); table_name: The name of the table you want to create. column1, column2, etc.: The names of the columns in the table. datatype: The data type for each column (e.g., INT, VARCHAR, DATE, etc.). constraints: Optional constraints (discussed in the next section) Example: Creating a simple table with basic columns: ...
Before we learn how to update data in SQL tables, let’s create two tables we will use: customers and orders. customers table USE ds2; # select database show tables; CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, age INT DEFAULT - 99, address VARCHAR(45), salary DECIMAL(18 , 2 ) DEFAULT 2000.00 ); orders table CREATE TABLE orders ( id INT NOT NULL, date datetime, customer_id INT REFERENCES customers (id), amount DOUBLE, PRIMARY KEY (id) ); Inserting Rows Updating Rows New rows may be inserted into a table one at a time using the SQL INSERT statement. A table name and column list are specified in the INTO clause, and a value list, enclosed in parentheses, is entered in the VALUES clause. ...