In this post, the shorts video of the ninth lecture of CS50 is summarized.

SQL

• SQL is a programming language whose purpose is to query a database.
• MySQL is one open-source platform on which you can establish the type of relational database that SQL is most adept at working with. SQLite is a more light weight version of it.
• Many installations of SQL come with a GUI tool called phpMyAdmin which can be used to execute database queries in a more user-friendly way.

creating table

• After you create a database, the next thing you’ll most likely want to do is create a table.
  • The syntax for doing this is actually a bit awkward to do programmatically, at least at the outset, and so this is where phpMyAdmin will come in handy.
• As part of the process of creating a talbe, you’ll be asked to specify all of the columns in that table.
• Thereafter, all your queries will refer to rows of the table.
• Each column of your SQL table is capable of holding data of a particular data type.

char vs varchar

• Unlike in C, the CHAR data type in SQL does not refer to a single character. Rather, it is a fixed-length string.
  • In most relational databases, including MySQL, you actually specify the fixed-length as part of the type definition, e.g. CHAR(10)
• A VARCHAR refers to a variable-length string. VARCHARs also require you to specify the maximum possible length of a string that could be stored in that column, e.g. VARCHAR(99)

data types

• SQLite has these data types as well, but affiliates each with a “type affinity” to simplify things.
• Every data types can be affiliated to one of 5 different affinities - NULL, INTEGER, REAL, TEXT, BLOB.

primary key

• One other important consideration when constructing a table in SQL is to choose one column to be your primary key.
• Primary keys enable rows of a talbe to be uniquely and quickly identified.
  • Choosing your primary key appropriately can make subsequent operations on the table much easier.
• It is also possible to establish a joint primary key - a combination of 2 columns that is always guaranteed to be unique.

INSERT

INSERT INTO table (columns) VALUES (values)
INSERT INTO users (username, password, fullname) VALUES ('newman', 'USMAIL', 'Newman')

• When defining the column that ultimately ends up being your talbe’s primary key, it’s usually a good idea to have that column be an integer.
• Moreover, so as to eliminate the situation where you may accidentally forget to specify a real value for the primary key column, you can configure that column to autoincrement, so it will pre-populate that column for you automatically when rows are added to the table.

SELECT

SELECT columns FROM table WHERE condition ORDER BY column
SELECT password FROM users WHERE idnum < 12

SELECT (JOIN)

• Databases empower us to organize information into tables efficiently.
  • We don’t always need to store every possible relevantn piece of information in the same table, but can use relationships across the tables to let us pull information from where we need it.
• What if we now find ourselves in a situation where we need to get a user’s full name (from the users table) and their mother’s name (from the mother table).

  SELECT columns FROM table1 JOIN table2 ON predicate
  SELECT users.fullname, moms.mother FROM users JOIN moms ON users.username = moms.username
  

UPDATE

UPDATE table SET column = value WHERE predicate
UPDATE users SET password = 'yadayada'
WHERE idnum = 10

DELETE

DELETE FROM table WHERE predicate
DELETE FROM users WHERE username = 'newman'