Create table

CREATE TABLE TABLE_NAME  ("CustomerID" INT, "PartySize" INT)

Insert data

INSERT INTO TABLE_NAME
(ID, NUMBER)
VALUES
(123,81)

INSERT INTO TABLE_NAME
(ID, NUMBER)
VALUES
((SELECT ID FROM COLUMN_NAME WHERE Email="1232@GMAIL.com"), 4)

Look for similarity

SELECT C.FirstName, C.LastName, Reservation.Date
FROM Reservation
JOIN Customer AS C ON C.ID = Reservation.ID
WHERE C.LastName LIKE "Ste%"

Check availablity

SELECT COUNT(Books.Title) FROM Loans JOIN Books ON Loads.BookID = Books.BookID
WHERE Books.Title = "Dracula" AND Loans.ReturnedDate IS NULL

Filter

_ underscore: a single character wildcard.

% percent sign: a wildcard for one or more characters.

* asterisk: represents zero or more characters

SQL string funcitons

SUBSTR( string, start, length )

LENGTH( string )

TRIM( string) #Trimming is case sensitive.

LOWER (string) #Convert the text to lower-case
SQL SUBSTR
SELECT released,
	SUBSTR(released, 1, 4) AS Year,
	SUBSTR(released, 6, 2) AS Month,
	SUBSTR(released, 9, 2) AS Day,
	FROM album ORDER BY released
SQL TRIM
SELECT TRIM('    string     ')
SELECT LTRIM('    string     ')
SELECT RTRIM('    string     ')
SELECT LTRIM('......string......', '.')

Some tips

  1. When INSERT data to a table, the unpopulated columns are given a null value rather than left blank.

  2. When use DELETE, the asterisk is not necessary for the code to remove a row from a table.

E.g:

DELETE FROM student WHERE id=3;
  1. How to just add a null row?
INSERT INTO TABLE_NAME DEFAULT VALUE
  1. How can a constraint be placed on a table where a field will contain the value “Mickey” if nothing is provided?
CREATE TABLE People (FirstName TEXT DEFAULT 'Mickey', address TEXT, city TEXT);
  1. Set an ID column
CREATE TABLE vehicles (VehicleID INTEGER PRIMARY KEY);
  1. Which constraint ensures a column cannot have any duplicate values including null values?

UNIQUE NOT NULL

  1. Alphabetical order: Z is the highest, and “A” is the lowest.

  2. To what does a table schema refer: the number of, titles for, and data types in columns

  3. Correct way to represent a string

SELECT 'Karen''s Classroom';

Two single quotes are used to represent one apostrophe and strings are represented in single quotes.

  1. SQLite compares data based on case, so is it important to convert all fields to the same case.

  2. What is the standard way to concatenate two strings?

Standard : ||

MySQL: CONCAT()

  1. Which option is an example of SQL syntax to add one day to the current day?
SELECT DATETIME('now', '+1 day');
  1. WHERE is used to filter non-aggregation data, HAVING is used to filter aggregation data.
  2. How transactions handled within a database: If any of the operations fail, then the entire group of operations fail.
  3. What does the CRUD operations acronym stand for?

Create, Read, Update and Delete.

References

LinkedIn Learning - SQL Code Challenges