SQL

Question 1

relational database

Answer 1

A relational database is a database that organizes information into one or more tables.

Question 2

SQL statement

Answer 2

SQL statement is text that the database recognizes as a valid command. Statements always end in a semi-colon ;.

Question 3

let’s create a new table in SQL

Answer 3

CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER); CREATE TABLE is a clause that tells SQL you want to create a new table. 2. celebs is the name of the table. 3. (id INTEGER, name TEXT, age INTEGER) is a list of parameters defining each column in the table and its data type. id is the first column in the table. It stores values of data type INTEGER name is the second column in the table. It stores values of data type TEXT age is the third column in the table. It stores values of data type INTEGER

Question 4

Add a row to the table.

Answer 4

INSERT INTO celebs (id, name, age) VALUES (1, ‘Justin Bieber’, 21);

Question 5

How to query only interested columns.

Answer 5

SELECT is used every time you want to query data from a database. SELECT column1, column2 FROM table_name;

Question 6

SELECT * FROM celebs;

Answer 6

You can also query data from all columns in a table with SELECT. SELECT statements always return a new table called the result set.

Question 7

let’s edit a row

Answer 7

UPDATE celebs SET age = 25 WHERE id = 1;

Question 8

Add a new column to the table.

Answer 8

ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;

Question 9

Delete all of the rows that have a NULL value in the twitter column.

Answer 9

DELETE FROM celebs WHERE twitter_handle IS NULL;

Question 10

Create a new table with constraints on the values

Answer 10

CREATE TABLE awards ( id INTEGER PRIMARY KEY, recipient TEXT NOT NULL, award_name TEXT DEFAULT “Grammy”);

Question 11

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. examples?

Answer 11

1. 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. 2. UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns. 3. 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. 4. 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.

Question 12

WHATS SQL

Answer 12

SQL is a programming language designed to manipulate and manage data stored in relational databases.One of the core purposes of the SQL language is to retrieve information stored in a database. This is commonly referred to as querying. A relational database is a database that organizes information into one or more tables. A table is a collection of data organized into rows and columns.

Question 13

how to renamed the “name” column to Movies in table “movies”

Answer 13

SELECT name AS ‘Movies’ FROM movies; AS is a keyword in SQL that allows you to rename a column or table using an alias. The new name can be anything you want as long as you put it inside of single quotes. It is important to remember that the columns have not been renamed in the table. The aliases only appear in the result.

Question 14

DISTINCT statement

Answer 14

DISTINCT is used to return unique values in the output. It filters out all duplicate values in the specified column(s).e.g. SELECT DISTINCT tools FROM inventory;

Question 15

WHERE clause

Answer 15

We can restrict our query results using the WHERE clause in order to obtain only the information we want. Following this format, the statement below filters the result set to only include top rated movies (IMDb ratings greater than 8): SELECT * FROM movies WHERE imdb_rating > 8;

Question 16

what is is an operator

Answer 16

Operators create a condition that can be evaluated as either true or false. Comparison operators used with the WHERE clause are: = equal to != not equal to > greater than < less than

Question 17

LIKE statement

Answer 17

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column. e.g. The movies table contains two films with similar titles, ‘Se7en’ and ‘Seven’. SELECT * FROM movies WHERE name LIKE ‘Se_en’; here, name LIKE ‘Se_en’ is a condition evaluating the name column for a specific pattern. Se_en represents a pattern with a wildcard character. The _ means you can substitute any individual character here without breaking the pattern. The names Seven and Se7en both match this pattern.

Question 18

wildcard character % and _

Answer 18

% is a wildcard character that matches zero or more missing letters in the pattern. This statement below filters the result set to only include movies with names that begin with the letter ‘A’: SELECT * FROM movies WHERE name LIKE ‘A%’; another e.g. , any movie that contains the word ‘man’ in its name will be returned in the result below. SELECT * FROM movies WHERE name LIKE ‘%man%’; LIKE is not case sensitive. ‘Batman’ and ‘Man of Steel’ will both appear in the result of the query above.

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

Question 19

To filter for all movies with an IMDb rating in the name column of table movies

Answer 19

SELECT name FROM movies WHERE imdb_rating IS NOT NULL;

Question 20

BETWEEN operator

Answer 20

BETWEEN operator can be used in a WHERE clause to filter the result set within a certain range. The values can be numbers, text or dates. This statement filters the result set to only include movies with names that begin with letters ‘A’ up to but not including ‘J’. SELECT * FROM movies WHERE name BETWEEN ‘A’ AND ‘J’; In this statement, the BETWEEN operator is being used to filter the result set to only include movies with years between 1990 up to and including 1999. SELECT * FROM movies WHERE year BETWEEN 1990 AND 1999; Really interesting point to emphasize again: BETWEEN two letters is not inclusive. BETWEEN two numbers is inclusive.

Question 21

how to combine multiple conditions in a WHERE clause to make the result set more specific and useful.

Answer 21

e.g. we use the AND operator to only return 90’s romance movies. SELECT * FROM movies WHERE year BETWEEN 1990 AND 1999 AND genre = ‘romance’;

Question 22

Suppose we want to check out a new movie or something action-packed:

Answer 22

SELECT * FROM movies WHERE year > 2014 OR genre = ‘action’;

Question 23

ORDER BY

Answer 23

ORDER BY always goes after WHERE! ORDER BY is a clause that indicates you want to sort the result set by a particular column. if we want to sort everything by the movie’s title from A through Z SELECT * FROM movies ORDER BY name; Sometimes we want to sort things in a decreasing order. For example, if we want to select all of the well-received movies, sorted from highest to lowest by their year: SELECT * FROM movies WHERE imdb_rating > 8 ORDER BY year DESC; DESC is a keyword used in ORDER BY to sort the results in descending order (high to low or Z-A). ASC is a keyword used in ORDER BY to sort the results in ascending order (low to high or A-Z). The column that we ORDER BY doesn’t even have to be one of the columns that we’re displaying.

Question 24

CASE statement

Answer 24

It is SQL’s way of handling if/then logic. A CASE statement allows us to create different outputs (usually in the SELECT statement). SELECT name, CASE WHEN imdb_rating > 7 THEN ‘Good’ WHEN imdb_rating > 5 THEN ‘Okay’ END FROM movies; Each WHEN tests a condition. The following THEN gives us the string if the condition is true. CASE statement must end with END. Here, we set all movies above 8 as ‘Good’ and movies above 5 ‘Okay’.

Question 25

code for - Use a CASE statement to create a column called mood that is: fun if genre is romance fun if genre is comedy serious in all other cases

Answer 25

SELECT name, CASE WHEN genre = ‘romance’ OR ‘comedy’ THEN ‘fun’ ELSE ‘serious’ END AS ‘mood’ FROM movies;

Question 26

what are aggregates. recall some important aggregates

Answer 26

Calculations performed on multiple rows of a table are called aggregates.

Here is a quick preview of some important aggregates

COUNT: count the number of values

SUM: add up all of the values in a column

MAX/MIN: get the largest/smallest value

AVG: get the mean for all values in a column

ROUND: round the values in the column

Question 27

The fastest way to calculate how many rows are in a table is to use what

Answer 27

The fastest way to calculate how many rows are in a table is to use the COUNT function.

COUNT is a function that takes the name of a column as an argument and counts the number of non-empty values in that column.

SELECT COUNT(*) FROM table_name;

Here, we want to count every row, so we pass *as an argument inside parenthesis.

Question 28

define SUM function

Answer 28

SUM is a function that takes the name of a column as an argument and returns the sum of all the values in that column.e.g.

SELECT SUM(downloads) FROM fake_apps;

This adds all values in the downloads column.

Question 29

define MAX and MIN function

Answer 29

MAX takes the name of a column as an argument and returns the largest value in that column. Here, we returned the largest value in the downloadscolumn.

MIN works the same way but it does the exact opposite; it returns the smallest value.e.g.

How many downloads does the most popular app have?

SELECT MAX(downloads) FROM fake_apps;

The most popular app has 31,090 downloads!

Question 30

ROUND function , How does it work

Answer 30

ROUND function takes two arguments inside the parenthesis:

a column name

an integer

It rounds the values in the column to the number of decimal places specified by the integer.

SELECT ROUND(price, 0) FROM fake_apps;

Here, we pass the column price and integer 0 as arguments. SQL rounds the values in the column to zero decimal places in the output.

Question 31

define GROUP BY clause

Answer 31

GROUP BY is a clause in SQL that is used with aggregate functions. It is used in collaboration with the SELECT statement to arrange identical data into groups.

The GROUP BY statement comes after any WHEREstatements, but before ORDER BY or LIMIT.e.g.

SELECT year, AVG(imdb_rating) FROM movies GROUP BY year ORDER BY year;

Question 32

how to Make the code here simplified -

we might want to know how many movies have IMDb ratings that round to 1, 2, 3, 4, 5. We could do this using the following syntax:

SELECT ROUND(imdb_rating),
COUNT(name)
FROM movies
GROUP BY ROUND(imdb_rating)
ORDER BY ROUND(imdb_rating);

However, this query may be time-consuming to write and more prone to error.

Answer 32

The following query is equivalent to the above:

SELECT ROUND(imdb_rating), COUNT(name) FROM movies GROUP BY 1 ORDER BY 1;

Here, the 1 refers to the first column in our SELECT statement, ROUND(imdb_rating)

SQL lets us use column reference(s) in our GROUP BY that will make our lives easier.

1 is the first column selected, 2 is the second column selected, and so on.

Question 33

Difference between WHERE and HAVING clause

Answer 33

HAVING is very similar to WHERE. In fact, all types of WHERE clauses you learned about thus far can be used with HAVING.

When we want to limit the results of a query based on values of the individual rows, we use WHERE.

When we want to limit the results of a query based on an aggregate property, we use HAVING.

HAVING statement always comes after GROUP BY, but before ORDER BY and LIMIT.e.g.

SELECT price,
ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price;

Certain price points don’t have very many apps, so the average is less meaningful.

Add a HAVING clause to restrict the query to prices where the total number of apps at that price point is greater than 9.

SELECT price,
ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price
HAVING COUNT(*) > 9;

Question 34

Suppose we have the three tables below. How do you combine table orders and table customers using join clause,

Answer 34

SELECT *

FROM orders

JOIN customers

ON orders.customer_id = customers.customer_id

If we only want to select certain columns, we can specify which ones we want.e.g.

SELECT orders.order_id, customers.customer_name

FROM orders

JOIN customers

ON orders.customer_id = customers.customer_id

Question 35

Describe what this code does -

SELECT *

FROM orders

JOIN subscriptions

ON orders.subscription_id = subscriptions.subscription_id

WHERE subscriptions.description = ‘Fashion Magazine’;

Answer 35

only selects rows from the join where description is equal to ‘Fashion Magazine’.

Again, WHERE statement is used to refine and limit the results of a query based on values of individual rows

Question 36

What are inner joins?

Answer 36

Consider an inner join of two tables on table1.key = table2.key:

suppose each table has 3 rows. The first and last rows have matching values of key. The middle rows do not match. The final result has all values form the first and last row, but does not include the non-matching middle row.

Question 37

What data does the code below retrieve

SELECT COUNT(\*)
FROM newspaper;

SELECT COUNT(\*)
FROM online;

SELECT COUNT(*)
FROM newspaper
JOIN online
ON newspaper.id = online.id;

Answer 37

Question 38

An inner JOIN does not return unmatched rows, while LEFT JOIN does. Describe how it works

Answer 38

A left join will keep all rows from the first table, regardless of whether there is a matching row in the second table. The final result will keep all rows of the first table but will omit the un-matched row from the second table.

Question 39

How do you define primary keys, and what are the requirements?

Answer 39

Def- a column in a table that uniquely identifies each row of that table.

Primary keys have a few requirements:

None of the values can be NULL

Each value must be unique (i.e., you can’t have two customers with the same customer_id in the customers table)

A table can not have more than one primary key column

Question 40

How do you define a foreign key? and how is it used the task of joining tables?

Answer 40

When the primary key for one table appears in a different table, it is called a foreign key. So customer_id is a primary key when it appears in customers, but a foreign key when it appears in orders.

The most common types of joins will be joining a foreign key from one table with the primary key from another table. For instance, when we join orders and customers, we join on customer_id, which is a foreign key in orders and the primary key in customers.

Question 41

What is a CROSS JOIN and how is it constructed?

Answer 41

CROSS JOIN is used to combine all rows of one table with all rows of another table. A cross join is used when you wish to create combination of every row from two tables. All row combinations are included in the result; this is commonly called cross product join. A common use for a cross join is to create obtain all combinations of items, such as colors and sizes. e.g

SELECT shirts.shirt_color, pants.pant_color

FROM shirts

CROSS JOIN pants;

The first two lines select the columns shirt_color and pant_color

The third line pulls data from the table shirts

The fourth line performs a CROSS JOINwith pants

Notice that cross joins don’t require an ONstatement. You’re not really joining on any columns!

If we have three different shirts (red, yellow, and blue) and two different pants (navy and black), the results might look like this:

shirt_color pant_color

red navy

red black

yellow navy

yellow black

blue navy

blue black

Question 42

A more common usage of CROSS JOIN is when we need to compare each row of a table to a list of values. how?

Answer 42

code

Let’s start by counting the number of customers who were subscribed to the newspaper during March (3).

– First query

SELECT COUNT(*)

FROM newspaper

WHERE start_month < 3

AND end_month > 3;

The previous query lets us investigate one month at a time. In order to check across all months, we’re going to need to use a cross join.

Our database contains another table called months which contains the numbers between 1 and 12. Select all columns from the cross join of newspaper and months.

– Second query

SELECT *

FROM newspaper

CROSS JOIN months;

Create a third query where you add a where statement to your cross join. The column month should be greater than start_month, but less than end_month. This will select all months where a user was subscribed.

– Third query

SELECT *

FROM newspaper

CROSS JOIN months

WHERE start_month < month

AND end_month > month;

Create a final query where you aggregate over each month.

– Fourth query

SELECT months.month,

COUNT(*)

FROM newspaper

CROSS JOIN months

WHERE start_month < month

AND end_month > month

GROUP BY months.month;

Question 43

What clause allows us to stack one dataset on top of the other.?

Answer 43

code

UNION -

Suppose we have two tables and they have the same columns:

table1

name email

Sonny sonny@foobar.com

Eric posture@boy.net

table2

name email

Chloe wallflower1991@gsnail.com

Ned sk8terboi@lotmail.com

SELECT *

FROM table1

UNION

SELECT *

FROM table2;

SQL has strict rules for appending data:

Tables must have the same # of columns.

The columns must have the same data types in the same order as the first table.

Question 44

what is and when is the WITH clause used

Answer 44

WITH allows us to define a bunch of temporary tables that can be used in the final query. i.e. allows us to combine two tables, when one of the tables is the result of another calculation.

The WITH statement allows us to perform a separate query (such as aggregating customer’s subscriptions)

previous_results is the alias that we will use to reference any columns from the query inside of the WITH clause

We can then go on to join our results with another table. e.g.

WITH previous_query AS (
SELECT customer_id,
COUNT(subscription_id) as subscriptions
FROM orders
GROUP BY customer_id)
SELECT customers.customer_name,
previous_query.subscriptions
FROM previous_query
JOIN customers
ON customers.customer_id = previous_query.customer_id;

Question 45

explain

SELECT last_name, first_name

FROM employees

WHERE last_name = ‘Johnson’

ORDER BY last_name DESC, first_name ASC;

Answer 45

When sorting your result set using the SQL Server ORDER BY clause, you can use the ASC and DESC attributes in a singleSELECT statement.This SQL Server ORDER BY would return all records sorted by the last_name field in descending order, with a secondary sort by first_name in ascending order.

Question 46

expln wht ths does

INSERT INTO contacts

(contact_id, last_name, first_name)

SELECT employee_id, last_name, first_name

FROM employees

WHERE first_name = ‘Joanne’

AND employee_id >= 800;

Answer 46

the AND condition can be used in the INSERT statement.

This SQL Server AND condition example would insert into the contacts table, all employee_id, last_name, and first_namerecords from the employees table where the first_name is ‘Joanne’ and the employee_id is greater than or equal to 800.

Question 47

When combining AND condition and the OR condition together, it is important to use parentheses so that the database knows what order to evaluate each condition.e.g.

Answer 47

SELECT employee_id, last_name, first_name

FROM employees

WHERE (last_name = ‘Smith’)

OR (last_name = ‘Anderson AND first_name = ‘Sarah’)

OR (employee_id > 1000 and state = ‘California’);

Question 48

expln

SELECT DISTINCT first_name, last_name

FROM employees

WHERE employee_id >=50

ORDER BY last_name;

Answer 48

This SQL Server DISTINCT clause example would return each unique first_name and last_name combination from the employees table where the employee_id is greater than or equal to 50. The results are sorted in ascending order by last_name.

Question 49

The SQL Server (Transact-SQL) IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement. e.g.

Answer 49

SELECT *

FROM employees

WHERE last_name IN (‘Smith’, ‘Anderson’, ‘Johnson’);

is th sme as

SELECT *

FROM employees

WHERE last_name = ‘Smith’

OR last_name = ‘Anderson’

OR last_name = ‘Johnson’;