learnxinyminutes sql

1-- Comments start with two hyphens. End each command with a semicolon.
2
3-- SQL is not case-sensitive about keywords. The sample commands here
4-- follow the convention of spelling them in upper-case because it makes
5-- it easier to distinguish them from database, table, and column names.
6
7-- Create and delete a database. Database and table names are case-sensitive.
8CREATE DATABASE someDatabase;
9DROP DATABASE someDatabase;
10
11-- List available databases.
12SHOW DATABASES;
13
14-- Use a particular existing database.
15USE employees;
16
17-- Select all rows and columns from the current database's departments table.
18-- Default activity is for the interpreter to scroll the results on your screen.
19SELECT * FROM departments;
20
21-- Retrieve all rows from the departments table,
22-- but only the dept_no and dept_name columns.
23-- Splitting up commands across lines is OK.
24SELECT dept_no,
25       dept_name FROM departments;
26
27-- Retrieve all departments columns, but just 5 rows.
28SELECT * FROM departments LIMIT 5;
29
30-- Retrieve dept_name column values from the departments
31-- table where the dept_name value has the substring 'en'.
32SELECT dept_name FROM departments WHERE dept_name LIKE '%en%';
33
34-- Retrieve all columns from the departments table where the dept_name
35-- column starts with an 'S' and has exactly 4 characters after it.
36SELECT * FROM departments WHERE dept_name LIKE 'S____';
37
38-- Select title values from the titles table but don't show duplicates.
39SELECT DISTINCT title FROM titles;
40
41-- Same as above, but sorted (case-sensitive) by the title values.
42SELECT DISTINCT title FROM titles ORDER BY title;
43
44-- Show the number of rows in the departments table.
45SELECT COUNT(*) FROM departments;
46
47-- Show the number of rows in the departments table that
48-- have 'en' as a substring of the dept_name value.
49SELECT COUNT(*) FROM departments WHERE dept_name LIKE '%en%';
50
51-- A JOIN of information from multiple tables: the titles table shows
52-- who had what job titles, by their employee numbers, from what
53-- date to what date. Retrieve this information, but instead of the
54-- employee number, use the employee number as a cross-reference to
55-- the employees table to get each employee's first and last name
56-- instead. (And only get 10 rows.)
57
58SELECT employees.first_name, employees.last_name,
59       titles.title, titles.from_date, titles.to_date
60FROM titles INNER JOIN employees ON
61       employees.emp_no = titles.emp_no LIMIT 10;
62
63-- List all the tables in all the databases. Implementations typically provide
64-- their own shortcut command to do this with the database currently in use.
65SELECT * FROM INFORMATION_SCHEMA.TABLES
66WHERE TABLE_TYPE='BASE TABLE';
67
68-- Create a table called tablename1, with the two columns shown, for
69-- the database currently in use. Lots of other options are available
70-- for how you specify the columns, such as their datatypes.
71CREATE TABLE tablename1 (fname VARCHAR(20), lname VARCHAR(20));
72
73-- Insert a row of data into the table tablename1. This assumes that the
74-- table has been defined to accept these values as appropriate for it.
75INSERT INTO tablename1 VALUES('Richard','Mutt');
76
77-- In tablename1, change the fname value to 'John'
78-- for all rows that have an lname value of 'Mutt'.
79UPDATE tablename1 SET fname='John' WHERE lname='Mutt';
80
81-- Delete rows from the tablename1 table
82-- where the lname value begins with 'M'.
83DELETE FROM tablename1 WHERE lname like 'M%';
84
85-- Delete all rows from the tablename1 table, leaving the empty table.
86DELETE FROM tablename1;
87
88-- Remove the entire tablename1 table.
89DROP TABLE tablename1;
90