Sql

Front 1

Creating a Table

Back 1

CREATE TABLE mytable

(

column_name1 data_type(size),

column_name2 data_type(size),

column_name3 data_type(size)

);

Front 2

Creating a new row

Back 2

INSERT INTO friends (id, name, birthday)

VALUES (1, 'Jane Doe', 'May 19th, 1993');

Front 3

Updating data

Back 3

UPDATE friends

SET name = "Jane Smith"

WHERE name = "Jane Doe";

Front 4

Adding a new column

Back 4

ALTER TABLE friends ADD COLUMN email TEXT;

Front 5

Deleting a row

Back 5

DELETE FROM friends WHERE id = 1;

or

DELETE FROM friends WHERE name = 'Billy Joe';

Front 6

What is Querying?

Back 6

To retrieve data from a database.

Front 7

What is the SELECT DISTINCT command/clause?

Back 7

Example:

SELECT DISTINCT genre FROM movies;

This will show all the genre types that have been added in that column.

SELECT DISTINCT is used to return unique values in the result set. It filters out all duplicate values.

SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in the specified column(s)

Front 8

I want to know only what movies have an 8+ rating in the movies table under the rating column.

How do you show me this?

Back 8

By using the WHERE clause and the > sign.

SELECT * FROM movies WHERE rating > 8;

Front 9

What is the LIKE command/clause?

Back 9

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

SELECT * FROM moviesWHERE name LIKE 'Se_en';

The _ means you can substitute any individual character here without breaking the pattern.

Front 10

When is the % wildcard used?

Back 10

When doing a search with LIKE

SELECT * FROM movies

WHERE name LIKE 'A%';

A% matches all movies with names that begin with "A"

%a matches all movies that end with "a"

Front 11

Why use two % wildcards?

'%man%'

Back 11

SELECT * FROM movies WHERE name LIKE '%man%';

You can use % both before and after a pattern. Here, any movie that contains the word "man" in its name will be returned in the result set

Front 12

is the LIKE clause case sensitive?

Back 12

NO

Front 13

What is the BETWEEN clause?

Back 13

The BETWEEN operator is used to filter the result set within a certain range. The values can be numbers, text or dates.

SELECT * FROM movies

WHERE year BETWEEN 1990 AND 2000;

The following will search through all the dates and only return movies that were published between the years 1990 and 2000!

Front 14

How do I narrow down a search to 2 columns?

Back 14

By using the AND clause.

Example:

SELECT * FROM moviesWHERE year BETWEEN 1990 AND 2000AND genre = 'comedy';

Front 15

How do you get both the result of comedy movies and horror movies?

Back 15

By using the OR clause

Example:

SELECT * FROM movies WHERE genre = 'horror' OR genre = 'comedy';

Front 16

How do you sort the results of your Query?

Back 16

You can sort the results of your query using ORDER BY. Sorting the results often makes the data more useful and easier to analyze.

Front 17

What is the DESC clause used for?

Back 17

DESC is used with ORDER BY to sort the results in descending order (high to low or Z-A).

Example:

SELECT * FROM moviesORDER BY imdb_rating DESC;

Sorts ratings from highest to lowest.

Front 18

What is the ASC clause used for?

Back 18

ASC is the opposite of DESC.

ASC is used with ORDER BY to sort the results in ascending order (low to high or A-Z).

SELECT * FROM moviesORDER BY imdb_rating ASCLIMIT 3;

Front 19

When do you use the LIMIT clause?

Back 19

LIMIT is a clause that lets you specify the maximum number of rows the result set will have

Example:

SELECT * FROM movies ORDER BY imdb_rating ASC LIMIT 3;

Front 20

What is the correct syntax to filter cities by temperatures less than 35?

Back 20

SELECT * FROM cities WHERE temperature > 35;

Front 21

What is ORDER BY?

Back 21

A clause that sorts the result set by a particular column either alphabetically or numerically.

Front 22

What will this do?

SELECT * FROM sports WHERE name LIKE '%ball';

Back 22

It matches all sports that end with "ball".

Front 23

What is LIMIT?

Back 23

A clause that lets you specify the maximum number of rows the result set will have.

Front 24

Explain what Aggregate Functions are.

Back 24

Aggregate Functions compute a single result from a set of input values. For instance, when we need the sum or average of a particular column, we can use aggregate functions to quickly compute it for us. We will be learning about different aggregate functions in this lesson.