4. Introduction to SQL

Working through an example:

To start MYSQL in Coursera labs go to the prompt and type mysql. You only need this if you’re using Coursera labs!

1. To check the state of the databases you have created:

SHOW DATABASES;

2. To create a new database called “Degree”

CREATE DATABASE Degree;

3. To use (go inside) this database

USE Degree;

4. To create a table inside Degree;

CREATE TABLE Lecturers (
Name VARCHAR(255),
DateOfBirth DATE,
DateJoined DATE,
PRIMARY KEY (Name, DateOfBirth)
);

Note – it’s possible that Name/DOB are not unique; the lecturer acknowledged this but used it anyway.

5. We can now show our tables:

SHOW TABLES;

6. and see it in more detail like this:

DESCRIBE Lecturers;

Note we get this returned:

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| Name        | varchar(255) | NO   | PRI | NULL    |       |
| DateOfBirth | date         | NO   | PRI | NULL    |       |
| DateJoined  | date         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

A Primary Key cannot be Null, but unless we specify otherwise a field could have a null value.

7. We can now insert a value

INSERT INTO Lecturers VALUES ('David Lewis', '1955-10-09', '1985-03-05');

8. If we now want to select all names from Lecturers:

SELECT Name FROM Lecturers;

This returns a table showing one record – David Lewis.

9. To see all fields from the table:

SELECT * FROM Lecturers;

10. We can add a constraint to a query:

SELECT Name FROM Lecturers WHERE DateJoined > '2000-01-01';

Tuesday 26 October 2021, 564 views

Next post: 5. Joins in SQL
Previous post: 3. Relational Databases