SQL Basics

SQL BASICS

In the middle of last month, I began my online SQL course. And we are now in the last week of the online course. It's a six weeks course. Since I have a programming background, I was able to pass all the quizzes with a 100% score each, except for two where I missed 1 question each. But since you can retake the quizzes, I got 100% in all of them.

Here is the basic summary of the course I'm taking.

SQL is the acronym for Structured Query Language, the standard language for communicating with a RDBMS or DBMS. RDBMS stands for Relational Database Management System. Examples of RDBMS and DBMS are Microsoft Access, MySQL, Microsoft SQL Server, Oracle, MariaDB, SQLite, IBM DB2, IBM Informix, Sybase, Teradata, Corel Paradox, dBase, LibreOffice Base, and FoxPro.

RDBMS and DBMS creates and stores databases. Databases consist of two dimensional tables. Tables consist of records and fields. Records and fields make up all the data about a certain information. Examples of this information are a student's permanent school record, your voting record, your cell phone data information, your library card record, and your hotel reservation to Disneyland.

Here is an example of a database table of records.

DATABASE TABLE NAME: Celebrities
CID FName  MName LName     City           State
1   DONALD JOHN  TRUMP     Washington     DC
2   JOHN        D        SINDAYEN  Las Vegas      NV
3   JOHN        F        KENNEDY   New Heaven     CT
4   JOHN                   KEATS        Valhalla       BC
5   JOHN                   DENVER     Denver         CO

Here are examples of SQL queries in the above database table.

SQL query to select everything in the database table:
SELECT FName, MName, LName, City, State
FROM Celebrities;
or
SELECT *
FROM Celebrities;

SQL query to count the number of records in the database table:
SELECT COUNT(CID) AS TotalRecords
FROM Celebrities;

SQL query to count the number of distinct first names in the database table:
SELECT COUNT(DISTINCT FName) AS DistinctFName
FROM Celebrities;

SQL query to count the number of cities between A and E.
SELECT COUNT(City) AS CityCount
FROM Celebrities
WHERE City BETWEEN 'A' AND 'E';

SQL query to add a new record to the database table as a processed transaction:
BEGIN TRANSACTION
INSERT INTO Celebrities (FName, MName, LName, City, State)
VALUES ('JOHNNY', NULL, 'DEPP', 'HOLLYWOOD', 'CA')
SAVE TRANSACTION InsertDepp
IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertDepp
COMMIT TRANSACTION

Transaction processing is not supported by all RDBMS and DBMS. Microsoft Access does not support transaction processing and it will probably not be able to do so in the future. But Microsoft Access is one of the easiest RDBMS around to work with and you don't have to be totally technically savvy to use it for SQL queries. You can also use it to create queries without writing SQL queries. You can just click the mouse to get the type of information you want out of the database table or tables.

Basic SQL keywords are the following, in no particular order:
SELECT
FROM
DISTINCT
WHERE
BETWEEN
LIKE
IN
AND
OR
NOT
ORDER BY
GROUP BY
HAVING
ASC
DESC
INSERT INTO
VALUES
IS NULL
NULL
UPDATE
SET
DELETE
FROM
AS
INNER JOIN
ON
LEFT JOIN
RIGHT JOIN
FULL JOIN
OUTER
CREATE TABLE
ALTER TABLE
ADD COLUMN
DROP TABLE
CREATE VIEW
DROP VIEW
BEGIN TRANSACTION
IF
ROLLBACK TRANSACTION
COMMIT TRANSACTION
PRIMARY KEY
UNIQUE
FOREIGN KEY
REFERENCES
CREATE INDEX
DROP INDEX
CREATE PROCEDURE
DECLARE
INTEGER
DROP PROCEDURE
CREATE DATABASE
DROP DATABASE
EXISTS
END
ELSE
SELECT TOP
TRUNCATE TABLE
UNION
UNION ALL

Basic SQL functions are the following:
COUNT
MIN
MAX
AVG
SUM
SQRT
RAND
CONCAT

May you find your answers to your queries,

John Sindayen