Informatics Practices · Ch 7 — Introduction to Structured Query Language (SQL)

Summary

A database is a collection of related tables; MySQL is a relational DBMS. A table is a collection of rows and columns, where each row is a record and the columns describe the features (attributes) of the records.
SQL is the standard language for most relational database management systems, and it is case insensitive — SELECT, select and SelecT all work the same way.
CREATE DATABASE creates a new database; USE makes the specified database the active one.
CREATE TABLE creates a table; every attribute in a CREATE TABLE statement must have a name and a datatype. Constraints restrict the values a column may hold — a primary key uniquely identifies each record, while a foreign key declares that an index (column) in one table is related to that in another table.
ALTER TABLE makes changes in the structure of a table — adding, removing, or changing the datatype of column(s). DESC followed by the table name shows the structure of the table.
INSERT INTO inserts record(s) in a table; UPDATE modifies existing data in a table; DELETE removes records from a table. In both UPDATE and DELETE, omitting the WHERE clause makes the statement act on every record of the table.
SELECT retrieves data from one or more database tables, displayed in tabular form; SELECT * FROM table_name displays data from all the attributes of the table.
The WHERE clause enforces condition(s) in a query, using relational operators (=, <, <=, >, >=, !=) combined with the logical operators AND, OR and NOT. String values in conditions are enclosed in quotes.
AS gives a column an alias — a display-only heading in the output (quoted if it contains a space); it does not add a new column to the stored table.
DISTINCT eliminates repetition and displays each value only once.
BETWEEN defines a range of values inclusive of both boundary values; IN selects values that match any value in a given list, and NOT IN excludes them. …

Start your 14-day free trial to unlock the full solution →

← Previous · 7.7.2Data Deletion Next →Q1 — Match the following clauses with their respective functions. | Clause …