SQL Basics

*

* is a special wildcard character that we have been using. It allows you to select every column in a table without having to name each one individually. Ex. SELECT *

Which clause is used with the ALTER TABLE statement?

ADD COLUMN

CREATE

CREATE statements allow us to create a new table in the database. You can use the CREATE statement anytime you want to create a new table from scratch.

clause

Clauses perform specific tasks in SQL. By convention, clauses are written in capital letters. Clauses can also be referred to as commands. ex. CREATE TABLE

Constraints

Constraints that add information about how a column can be used are invoked after specifying the data type for a column. They can be used to tell the database to reject inserted data that does not adhere to a certain restriction.

IS NULL

IS NULL is a condition in SQL that returns true when the value is NULL and false otherwise

How to download SQLite

Mac OS X For Macs, use the Mac OS X (x86) sqlite-tools package: 1. Install it, and unzip it. 2. In your terminal, navigate to the directory of the unzipped folder using cd. 3. Run the command mv sqlite3 /usr/local/bin/. This will add the command sqlite3to your terminal path, allowing you to use the command from anywhere. 4. Try typing sqlite3 newdb.sqlite. If you're presented with a sqlite> prompt, you've installed SQLite! Enter control + d to quit. You can also exit by typing .exit in the prompt and pressing return. Video demonstration: Windows https://youtu.be/dcfh5iQ_-3s Mac iOS https://youtu.be/4MJSZi4qvIE

SQL Server

Microsoft owns SQL Server. Like Oracle DB, the code is close sourced. Large enterprise applications mostly use SQL Server. Microsoft offers a free entry-level version called Express but can become very expensive as you scale your application.

MySQL

MySQL is the most popular open source SQL database. It is typically used for web application development, and often accessed using PHP. The main advantages of MySQL are that it is easy to use, inexpensive, reliable (has been around since 1995), and has a large community of developers who can help answer questions. Some of the disadvantages are that it has been known to suffer from poor performance when scaling, open source development has lagged since Oracle has taken control of MySQL, and it does not include some advanced features that developers may be used to.

Popular RDBMS

MySQL, PostgreSQL, Oracle DB, SQLite

Oracle DB

Oracle Corporation owns Oracle Database, and the code is not open sourced. Oracle DB is for large applications, particularly in the banking industry. Most of the world's top banks run Oracle applications because Oracle offers a powerful combination of technology and comprehensive, pre-integrated business applications, including essential functionality built specifically for banks. The main disadvantage of using Oracle is that it is not free to use like its open source competitors and can be quite expensive.

PostgreSQL

PostgreSQL is an open source SQL database that is not controlled by any corporation. It is typically used for web application development. PostgreSQL shares many of the same advantages of MySQL. It is easy to use, inexpensive, reliable and has a large community of developers. It also provides some additional features such as foreign key support without requiring complex configuration. The main disadvantage of PostgreSQL is that it is slower in performance than other databases such as MySQL. It is also less popular than MySQL which makes it harder to come by hosts or service providers that offer managed PostgreSQL instances.

RDBMS

Relational Database Management System

SELECT

SELECT statements are used to fetch data from a database. It is a clause that indicates that the statement is a query. You will use SELECT every time you query data from a database. SELECT statements always return a new table called the result set. EX. In the statement below, SELECT returns all data in the name column of the celebs table. --> SELECT name FROM celebs;

SQLite

SQLite is a popular open source SQL database. It can store an entire database in a single file. One of the most significant advantages this provides is that all of the data can be stored locally without having to connect your database to a server. SQLite is a popular choice for databases in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets. The SQL courses on Codecademy use SQLite. For more info on SQLite, including installation instructions, read this article: https://www.codecademy.com/courses/learn-sql/articles/what-is-sqlite

SQL

Structured Query Language

INSERT

The INSERT statement inserts a new row into a table. You can use the INSERT statement when you want to add new records.

UPDATE

The UPDATE statement edits a row in a table. You can use the UPDATE statement when you want to change existing records.

WHERE

WHERE is a clause that indicates which row(s) to update with the new column value. Here the row with a 4 in the id column is the row that will have the twitter_handle updated to @taylorswift13

INSERT INTO

a clause that adds the specified row or rows.

VALUES

a clause that indicates the data being inserted.

DELETE FROM

a clause that lets you delete rows from a table.

table

a collection of data organized into rows and columns. Tables are sometimes referred to as relations

REAL

a decimal value (a data type)

parameter

a list of columns, data types, or values that are passed to a clause as an argument

INTEGER

a positive or negative whole number (a data type)

relational database management system (RDBMS)

a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database

SQL (Structured Query Language)

a programming language used to communicate with data stored in a relational database management system.

database

a set of data stored in a computer. This data is usually structured in a way that makes the data easily accessible

Rows

a single record in a table

NULL

a special value in SQL that represents missing or unknown data. Here, the rows that existed before the column was added have NULL (∅) values for twitter_handle.

TEXT

a text string (a data type)

relational database

a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables

PRIMARY KEY

columns can be used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.

UNIQUE

columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.

NOT NULL

columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.

DEFAULT

columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

ALTER TABLE

he ALTER TABLE statement adds a new column to a table. You can use this command when you want to add columns to a table.

SET

is a clause that indicates the column to edit

Columns

is a set of data values of a particular type. Labeled with a descriptive name (say, age for example) and have a specific data type. ex. For example, a column called age may have a type of INTEGER (denoting the type of data it is meant to hold)

statement

text that the database recognizes as a valid command. Always ending in a semicolon ;

DATE

the date formatted as YYYY-MM-DD (a data type)