6.1.1.Basic SQL - sj50179/IBM-Data-Science-Professional-Certificate GitHub Wiki

WHERE Clause Comparison Operators

SELECT boo_id, title
FROM Book
WHERE book_id = 'B1'
Description Operator
Equal to =
Greater than >
Lesser than <
Greater than or equal to >=
Less than or equal to <=
Not equal to <>

SELECT statement examples

The general syntax of SELECT statments is:

select COLUMN1, COLUMN2, ... from TABLE1 ;

To retrieve all columns from the COUNTRY table we could use * instead of specifying individual column names:

select * from COUNTRY ;

The WHERE clause can be added to your query to filter results or get specific rows of data. To retrieve data for all rows in the COUNTRY table where the ID is less than 5:

select * from COUNTRY where ID < 5 ;

In case of character based columns the values of the predicates in the where clause need to be enclosed in single quotes. To retrieve the data for the country with country code 'CA' we would issue:

select * from COUNTRY where CCODE = 'CA' ;

Hands-on Lab: Simple SELECT Statements

COUNT, DISTINCT, LIMIT

COUNT

SELECT COUNU(*) FROM tablename

Example:

Rows in the MEDALS table where Country is Canada

SELECT COUNT(COUNTRY)
FROM MEDALS
WHERE COUNTRY = 'Canada'

DISTINCT

Used to remove duplicate values from a result set

SELECT DISTINCT columnname
FROM tablename

Example:

List of unique countries that received GOLD medals

SELECT DISTINCT COUNTRY
FROM MEDALS
WHERE MEDALTYPE = 'GOLD'

LIMIT

Used for restricting the number of rows retrieved from the database

SELECT * 
FROM tablename
LIMIT 10

Example:

Retrieve 5 rows in the MEDALS table for a particular year

SELECT *
FROM MEDALS
WHERE YEAR = 2018
LIMIT 5

Hands-on Lab: COUNT, DISTINCT, LIMIT

INSERT Statement

Adding rows to a table

  • Create the table (CREATE TABLE statement)
  • Populate table with data:
    • INSERT statement
      • A Data Manipulation Language (DML) statement used to read and modify data

Using the INSERT Statement

INSERT INTO TableName
	(ColumnName1, ...)
VALUES
	(Value1_1, ...)
	(Value2_1, ...)

UPDATE and DELETE Statements

Altering rows of a table - UPDATE statement

  • After creating a table and inserting data into the table, we can alter the data
    • UPDATE statement: A Data Manipulation Language (DML) statement used to read and modify data

Using the UPDATE Statement

UPDATE TableName
SET ColumnName = Value
WHERE condition 
-- If the WHERE clause is omitted, all the rows in the table are updated.

-- Example
UPDATE Author
SET LastName = 'James',	FirstName = 'Mike'
WHERE Author_Id = 'A2'

Deleting Rows from a table

  • Remove 1 or more rows from the table:
    • DELETE statement
      • A DML statement used to read and modify data
DELETE FROM TableName
WHERE condition

-- Example
DELETE FROM Author
WHERE Author_Id IN ('A2', 'A3')

Hands-on Lab : INSERT, UPDATE, DELETE

Practice Quiz

TOTAL POINTS 5

Question 1

Which of the following statements are correct about databases?

  • here are different types of databases - Relational, Hierarchical, No SQL, etc.
  • A database can be populated with data and be queried
  • A database is a repository of data
  • All of the above

Correct. All of the above statements are true.

Question 2

True or False: A SELECT statement is used to retrieve data from a table.

  • True
  • False

Correct. A SELECT statement is used to retrieve data from a table.

Question 3

You are working on a Film database, with a FilmLocations table. You want to retrieve a list of films that were released in 2019.You run the following query but find that all the films in the FilmLocations table are listed.

SELECT Title,ReleaseYear, Locations FROMFilmLocations;

What is missing?

  • A LIMIT clause to limit the results to films released in 2019.
  • Nothing, the query is correct.
  • A DINSTINCT clause to specify a distinct year.
  • A WHERE clause to limit the results to films released in 2019.

Correct. The query needs a WHERE clause like WHERE ReleaseYear=2019*.*

Question 4

Which of the following statements would you use to add a new instructor to the Instructor table.

  • SELECT Instructor(ins_id, lastname, firstname, city, country) FROM VALUES(4, 'Doe', 'John', 'Sydney', 'AU');
  • ADD INTO Instructor(ins_id, lastname, firstname, city, country) VALUES(4, 'Doe', 'John', 'Sydney', 'AU');
  • UPDATE Instructor(ins_id, lastname, firstname, city, country) WITH VALUES(4, 'Doe', 'John', 'Sydney', 'AU');
  • INSERT INTO Instructor(ins_id, lastname, firstname, city, country) VALUES(4, 'Doe', 'John', 'Sydney', 'AU');

Correct. The INSERT INTO statement is used to add rows to a table.

Question 5

What is the function of a WHERE clause in an UPDATE statement?

  • A WHERE clause enables you to list the column and data to be updated.
  • A WHERE clause is never used with an UPDATE statement.
  • A WHERE clause enables you to specify which rows will be updated.
  • A WHERE clause enables you to specify a new table to receive the updates.

Correct. A WHERE clause is used to restrict the rows in the result set. If you run a UPDATE statement without a WHERE clause, you will update all the rows in the table.