learn sql quickly: Data and Tables

Learn through examples.

You can use heroku to access database or you can install locally.

psql url
  •  A database is a container that holds tables and other SQL structures related to those tables.
  •  A table is the structure inside your database that contains data, organized in columns and rows.
  •  A table row contains all the information about one object in your table.
  • The information inside the database is organize into TABLES.
  • Database and table names are not usually capitalized.
  • A column is a piece of data stored by your table. A row is a single set of columns that describe attributes of a single thing. Columns and rows together make up a table.
  1. Create a database

CREATE DATABASE db_name;

Spaces aren’t allowed in the names of database and tables in SQL, so an underscore can be used instead.

Your command must end with a semicolon.

 Now you need to tell your RDBMS to actually use the database you just created:

USE db_name;

Now everything we do will happen inside the db_name database!

CREATE TABLE doughnut_list

(

doughnut_name VARCHAR(10),

doughnut_type VARCHAR(6)

);
  •  VARCHAR is a DATA TYPE. It stands for VARiable CHARacter and is used to hold information that’s stored as text.The (6) means that the text it holds can be up to 6 characters long.
CREATE TABLE my_contacts

(

first_name VARCHAR(30),

last_name VARCHAR(20),

email VARCHAR(50),

birthday DATE,

profession VARCHAR(50),

location VARCHAR(50),

status VARCHAR(20),

interests VARCHAR(100),

seeking VARCHAR(100)

);
  • DEC(5,2) – > These two numbers show how many digits the database should expect in front of the decimal, and how many after.
  • CHAR(10) –> A phone number will always be exactly this length and we treat it like a text string, because we don’t need to do any mathematical operations on it, even though it’s a number.
  • DATETIME is usually used to capture the current time DATETIME is besr used to store a future event.

DROP TABLE db_name;

  • The command to delete your table and name of the table to be deleted.
  • Don’t forget the semicolon.
  • DROP TABLE deletes your table and any data in it!
  •  DROP TABLE will work whether or not there is data in your table, so use the command with extreme caution. Once your table is dropped, it’s gone, along with any data that was in it.

To add data to your table, you’ll use the INSERT statement

INSERT INTO table_name (column_name1, column_name2, …) VALUES (‘value1’, ‘value’ …);

Note –> The values need to be in the same order as the column names.

INSERT INTO my_contacts (first_name, last_name, email, birthday, profession, location, status, interests, seeking)

VALUES

('Amit', 'Singh', '[email protected]', '1993-05-02', 'Software developer', 'Bihar', 'Single', 'Hacking', 'Dream life');

Variations on an INSERT statement

1. Changing the order of columns

You can change the order of your column names as long as the matching values for each column come in that same order!

2. Omitting column names

You can leave out the list of column names, but the values must be all there, and all in the same order that you added the columns in.

3. Leaving some columns out

You can insert a few columns and leave some out.

Peek at your table with the SELECT statement

SELECT * FROM table_name;

–> We want to select all the data in our table and the asterisk says to select EVERYTHING.

Controlling your inner NULL

CREATE TABLE table_name

(

column1 VARCHAR (30) NOT NULL, #Just add the words NOT NULL right after the data type.

column2 VARCHAR (20) NOT NULL,

);

–> If you use these, you must provide a value for the column in your INSERT statement If you don’t you’ll get an error.

Working with DEFAULT

If we have a column that we know is usually a specific value, we can assign it a DEFAULT value. The value that follows the DEFAULT keyword is automatically inserted into the table each time a row is added if no other value is specified. The default value has to be of the same type of value as the column.

CREATE TABLE doughnut_list(

doughnut_name VARCHAR(10) NOT NULL,

doughnut_type VARCHAR(6) NOT NULL,

doughnut_cost DEC(3,2) NOT NULL DEFAULT 1.00

);
  • We want to make sure that we always have a value in this column. Not only can we make it NOT NULL , we can also assign it a DEFAULT value of 1.
  • Using a DEFAULT value fills the empty columns with a specified value.

KEY POINTS FROM CHARACTER 1

CREATE DATABASE

Use this statement to set up the database that will hold all your tables.

USE DATABASE

Gets you inside the database to set up all your tables.

CREATE TABLE

Starts setting up your table, but you’ll also need to know your COLUMN NAMES and DATA TYPES. You should have worked these out by analyzing the kind of data you’ll be putting in your table.

NULL and NOT NULL

You’ all also need to have an idea which columns should not accept NULL values to help you sort and search your data. You’ll need to set the columns to NOT NULL when you create your table.

DEFAULT

Lets you specify a default value for a column, used if you don’t supply a value for the column when you insert a record.

DROP TABLE

Lets you delete a table of you make a mistake, but you’ll need to do this before you start using INSERT statements, which let you add the values for each column.

IMPORTANT POINTS

  •  If you want to see the structure of your table, use the DESC statement.
  •  The DROP TABLE statement can be used to throw away your table. Use it with care!
  • To get your data inside your table, use one of the several varieties of INSERT statements.
  • A NULL value is an undefined value. It does not equal zero or an empty value. A column with a NULL value IS NULL, but does not EQUAL NULL.
  •  Columns that are not assigned values in your INSERT statements are set to NULL by default.
  • You can change a column to not accept a NULL value by using the keywords NOT NULL when you create your table.
  •  Using a DEFAULT value when you CREATE your table fills the column with that value if you insert a record with no value for that column.
Head First SQL is a great book TO learn SQL.

Leave a Reply