udacity sql

The LIKE operator is frequently used with

%

spanish in sql

'spanish';

Drop the firstname and lastname columns from the affiliations table.

(Drop the firstname column ) Alter table affiliations DROP column firstname; (Drop the lastname column ) Alter table affiliations DROP column lastname;

Join the tables countries (left) and economies (right) aliasing countries AS c and economies AS e. Specify the field to match the tables ON. From this join, SELECT:c.code, aliased as country_code.name, year, and inflation_rate, not aliased.

- Select fields with aliases SELECT c.code AS country_code, name, year, inflation_rate FROM countries AS c -- Join to economies (alias e) INNER JOIN economies AS e -- Match on code ON c.code = e.code;

If you want more precision when dividing, you can add decimal places to your numbers. For example, SELECT (4.0 / 3.0). the result you would expect:

1.333

write $2 million in sql

2000000 (no dollar sign in front, no dashes )

SELECT 45 / 10 * 100.0;

400.0

Make id a primary key and name it professors_pkey.

ALTER table professors add CONSTRAINT professors_pkey primary key (id)

Surround the operation with a TRY block. Surround the functions with a CATCH block. Select the error information.

BEGIN TRY SELECT 'Total: ' + SUM(price * quantity) AS total FROM orders END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS number, ERROR_SEVERITY() AS severity_level, ERROR_STATE() AS state, ERROR_LINE() AS line, ERROR_MESSAGE() AS message; END CATCH

Create a role called admin with the ability to create databases (CREATEDB) and to create roles (CREATEROLE).

CREATE ROLE admin WITH CREATEDB CREATEROLE;

Create a table professors with two text columns: firstname and lastname

CREATE TABLE professors ( firstname text, lastname text ); SELECT * FROM professors

For the inner function, turn the date the Cubs won the World Series into a DATE data type using the CAST() function. For the outer function, reshape this date as an NVARCHAR(30) using the CAST() function.

DECLARE @CubsWinWorldSeries DATETIME2(3) = '2016-11-03 00:30:29.245' SELECT CAST(CAST(@CubsWinWorldSeries AS DATE) AS NVARCHAR(30)) AS Datestringform;

Use the CONVERT() function to translate the date the Cubs won the world series into the DATE and NVARCHAR(30) data types. The functional form for CONVERT() is CONVERT(DataType, SomeValue).

DECLARE @CubsWinWorldSeries DATETIME2(3) = '2016-11-03 00:30:29.245' SELECT CONVERT(DATE, @CubsWinWorldSeries) AS CubsWinDateForm, CONVERT(NVARCHAR(30), @CubsWinWorldSeries) AS CubsWinStringForm;

Split the phrase declared in the variable @phrase into sentences (using the . separator).

DECLARE @phrase NVARCHAR(MAX) = 'In the morning I brush my teeth. In the afternoon I tak SELECT value FROM STRING_SPLIT(@phrase, '.');

Extract the fruit names from the following sentence: "Apples are neither oranges nor potatoes".

DECLARE @sentence NVARCHAR(200) = 'Apples are neither oranges nor potatoes.' SELECT SUBSTRING(@sentence, 1, 6) AS fruit1, SUBSTRING(@sentence, 20, 7) AS fruit2;

For table deletion, you can use the simple command:

DROP TABLE table_name;

The analytical functions that return the first or last value from an ordered list prove to be very helpful in queries. In this exercise, you will get familiar with them. The syntax is:

FIRST_VALUE(numeric_expression) OVER ([PARTITION BY column] ORDER BY column ROW_or_RANGE frame) LAST_VALUE(numeric_expression) OVER ([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)

Grant the data_scientist role update and insert privileges on the long_reviews view. Alter Marta's role to give her the provided password.

GRANT UPDATE, INSERT ON long_reviews TO data_scientist; ALTER ROLE marta WITH PASSWORD 's3cur3p@ssw0rd';

Add Marta's user role to the data scientist group role. Celebrate! You hired multiple data scientists. Remove Marta's user role from the data scientist group role.

GRANT data_scientist TO marta; REVOKE data_scientist FROM marta;

Update the query to return only the records where the minimum of DurationSeconds column is greater than 1.

HAVING MIN(DurationSeconds) > 1

migrate the data into the new tables. You'll use the following pattern:

INSERT INTO ... SELECT DISTINCT table 1 FROM table 2;

Insert all DISTINCT affiliations into affiliations from university_professors.

Insert into affiliations SELECT distinct firstname, lastname, function, organization FROM university_professors; SELECT * FROM affiliations;

Insert all DISTINCT professors from university_professors into professors.

Insert into professors SELECT DISTINCT firstname, lastname, university_shortname FROM university_professors; SELECT * FROM professors;

By using the LAG() function in a query, you can access rows previous to the current one. This is the syntax:

LAG(numeric_expression) OVER ([PARTITION BY column] ORDER BY column)

should you use plural in sql queries.

No use singular and add unique for other specific things.

Join week_dim and runs_fact. Get all the week_id's from July, 2019.

SELECT -- Get the total duration of all runs SUM(duration_mins) FROM runs_fact -- Get all the week_id's that are from July, 2019 INNER JOIN week_dim ON week_dim.week_id = runs_fact.week_id WHERE month = 'July' and year = '2019';

Calculate what day it was 476 days ago.

SELECT DATEADD(DAY, -476, GETDATE()) AS date_476days_ago;

How many weeks have passed since the beginning of 2019 until now?

SELECT DATEDIFF(WEEK, '2019-01-01', GETDATE()) AS weeks_passed;

Calculate the average percentage of cocoa used by each company.

SELECT company, AVG(cocoa_percent) AS avg_cocoa FROM ratings GROUP BY company;

Restrict the query to show only the voters did not vote on the first day of the month.

SELECT first_name, last_name, YEAR(first_vote_date) AS first_vote_year, MONTH(first_vote_date) AS first_vote_month, DAY(first_vote_date) AS first_vote_day FROM voters WHERE YEAR(first_vote_date) > 2015 AND DAY(first_vote_date) <> 1;

Extract the weekday from the first_vote_date.

SELECT first_name, last_name, birthdate, first_vote_date, DATENAME(weekday, first_vote_date) AS first_vote_weekday FROM voters;

Count the number of voters for each group. Calculate the total number of votes per group.

SELECT gender, COUNT(*) AS voters, SUM(total_votes) AS total_votes FROM voters GROUP BY gender;

Round up the ratings to the nearest integer value.

SELECT rating, CEILING(rating) AS round_up FROM ratings;

Round the ratings to a decimal number with only 1 decimal.

SELECT rating, CEILING(rating) AS round_up, FLOOR(rating) AS round_down, round(rating, 1) AS round_onedec FROM ratings;

to return all rows with non-missing SHAPE values, you can use:

SELECT * FROM Incidents WHERE Shape IS NOT NULL

Find all the company names that start with a 'C' or 'W', and the primary contact contains 'ana' or 'Ana', but it doesn't contain 'eana'.

SELECT * FROM accounts WHERE (name LIKE 'C%' OR name LIKE 'W%') AND ((primary_poc LIKE '%ana%' OR primary_poc LIKE '%Ana%') AND primary_poc NOT LIKE '%eana%');

Write a query that converts all the negative values in the DeliveryWeight column to positive values.

SELECT DeliveryWeight, ABS(DeliveryWeight) AS AbsoluteValue FROM Shipments

the highest budget

SELECT MAX(budget) FROM films;

Create the window, partition by TerritoryName and order by OrderDate to calculate a running standard deviation of OrderPrice.

SELECT OrderDate, TerritoryName, STDEV(OrderPrice) OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS StdDevprice FROM Orders

total budget of movies made in the year 2010 or later:

SELECT SUM(budget) FROM films WHERE release_year >= 2010;

Retrieve the birth date from voters, in this format: Mon dd,yyyy

SELECT email, CONVERT(varchar, birthdate, 107) AS birthdate FROM voters;

Restrict the query to retrieve only the female voters who voted more than 20 times.

SELECT first_name, last_name, gender, country FROM voters WHERE country = 'Belgium' AND gender = 'F' AND total_votes > 20;

Select the voters whose total number of votes starts with 5.

SELECT first_name, last_name, total_votes FROM voters - WHERE CAST(total_votes AS char(10)) LIKE '5%';

Find list of orders ids where either gloss_qty or poster_qty is greater than 4000. Only include the id field in the resulting table.

SELECT id FROM orders WHERE gloss_qty > 4000 OR poster_qty > 4000;

All companies whose names contain the string 'one' somewhere in the name.

SELECT name FROM accounts WHERE name LIKE '%one%';

All companies whose names end with 's'.

SELECT name FROM accounts WHERE name LIKE '%s';

All the companies whose names start with 'C'

SELECT name FROM accounts WHERE name LIKE 'C%';

titles of films released between 1994 and 2000

SELECT title FROM films WHERE release_year > 1994 AND release_year < 2000;

Select the title column from the album table where the album_id is 213.

SELECT title FROM album WHERE album_id = 213;

all films released in either 1994 or 2000

SELECT title FROM films WHERE release_year = 1994 OR release_year = 2000;

all films that were released in 1994 or 1995 which had a rating of PG or R.

SELECT title FROM films WHERE (release_year = 1994 OR release_year = 1995) AND (certification = 'PG' OR certification = 'R');

titles of all films released in and between 1994 and 2000:

SELECT title FROM films WHERE release_year >= 1994 AND release_year <= 2000;

add an integer type cast at the right place and execute it again.

SELECT transaction_date, amount + CAST(fee AS integer) AS net_am FROM transactions;

Write a WHILE loop that increments counter by 1 until counter is less than 30.

SET @counter = 20 WHILE @counter < 30 BEGIN SELECT @counter = @counter + 1 END SELECT @counter

Get all details for Spanish language films released after 2000.

Select * from films where release_year > 2000 AND language = 'Spanish';

Get the title and language of all films which were in English, Spanish, or French.

Select title, language from films where language IN ('English', 'Spanish', 'French');

specify a fixed-length character type with the correct length for university_shortname.

TYPE char(3);

Update the professor_id column with the corresponding value of the id column in professors."Corresponding" means rows in professors where the firstname and lastname are identical to the ones in affiliations.

UPDATE affiliations SET professor_id = professors.id FROM professors WHERE affiliations.firstname = professors.firstname AND affiliations.lastname = professors.lastname;

That's a very long album title, isn't it? Use an UPDATE statement to modify the title to 'Pure Cult: The Best Of The Cult'.

UPDATE album -- SET the new title SET title = 'Pure Cult: The Best Of WHERE album_id = 213;

Concatenate make and model into id using an UPDATE table_name SET column_name = ... query and the CONCAT() function.

UPDATE cars SET id = CONCAT(make, model);

update columns of a table based on values in another table:

UPDATE table_a SET column_to_update = table_b.column_to_update_from FROM table_b WHERE condition1 AND condition2 AND ...;

use SUBSTRING() to reduce firstname to 16 characters so its type can be altered to varchar(16).

USING SUBSTRING(firstname FROM 1 FOR 16);

Create a CTE BloodGlucoseRandom that returns one column (MaxGlucose) which contains the maximum BloodGlucoseRandom in the table. Join the CTE to the main table (Kidney) on BloodGlucoseRandom and MaxGlucose.

WITH BloodGlucoseRandom (MaxGlucose) AS (SELECT MAX(BloodGlucoseRandom) AS MaxGlucose FROM Kidney) SELECT a.Age, b.MaxGlucose FROM Kidney a JOIN BloodGlucoseRandom b ON a.BloodGlucoseRandom = b.MaxGlucose

SQRT(number)

calculates the square root of a positive number.

DESC

descending order

unique

distinct

Return a list of unique countries using DISTINCT. Give the results an alias of unique_country.

distinct country AS unique_country

You can also use the NOT LIKE operator to

find records that don't match the pattern you specify.

In joining tables the first column is the primary key and the last column is the

foreign key. If these two columns match then you can join them using the word JOIN.

Primary keys should

have no repeats

SQUARE(number)

raises the number to the power of 2

Get the title for every film, in reverse order.

select title from films order by title DESC;

ORDER BY

will sort in ascending order. If you want to sort the results in descending order, you can use the DESC keyword. For example, SELECT title FROM films ORDER BY release_year DESC;

Change the type of the firstname column to varchar(64).

ALTER TABLE PROFESSORS ALTER COLUMN firstname TYPE VARCHAR(64);

Add a professor_id column with integer data type to affiliations, and declare it to be a foreign key that references the id column in professors.

ALTER TABLE affiliations ADD COLUMN professor_id integer REFERENCES professors (id);

Add a foreign key constraint on organization_id so that it references the id column in organizations.

ALTER TABLE affiliations ADD CONSTRAINT affiliations_organization_fkey FOREIGN KEY (organization_id) REFERENCES organization (id);

Rename the organisation column to organization in affiliations.

ALTER TABLE affiliations RENAME column organisation TO organization;

Delete the university_shortname column in affiliations.

ALTER TABLE affiliations drop COLUMN university_shortname;

Make id a primary key and name it id_pk.

ALTER TABLE cars add constraint id_pk primary key(id);

Add a new column id with the data type varchar(128).

ALTER TABLE cars add column id varchar (28);

Rename the organization column to id in organizations.

ALTER TABLE organizations RENAME COLUMN organization TO id;

Make id a primary key and name it organization_pk.

ALTER TABLE organizations ADD CONSTRAINT organization_pk PRIMARY KEY (id);

Add a unique constraint to the organization column in organizations. Give it the name organization_unq.

ALTER TABLE organizations ADD CONSTRAINT organization_unq UNIQUE(organization);

Add a new column id with data type serial to the professors table.

ALTER TABLE professors add column id serial;

Create a role called data_scientist.

CREATE ROLE data_scientist;

Create a role called marta that has one attribute: the ability to login (LOGIN).

CREATE ROLE marta LOGIN;

A student has: a last name consisting of up to 128 characters (required), a unique social security number, consisting only of integers, that should serve as a key, a phone number of fixed length 12, consisting of numbers and characters (but some students don't have one).

CREATE TABLE students ( last_name varchar(128) NOT NULL, ssn integer PRIMARY KEY, phone_no char(12) );

Create a table called tracks with 2 VARCHAR columns named track and album, and one integer column named track_length_mins. Then, select all columns from the new table using the * notation.

CREATE TABLE tracks( -- Create track column track VARCHAR(200), -- Create album column album VARCHAR(160), -- Create track_length_mins col track_length_mins INT ); -- Select all columns from the ne SELECT * FROM tracks;

Create a table universities with three text columns: university_shortname, university, and university_city.

CREATE TABLE universities ( university_shortname text, university text, university_city text ); SELECT * FROM universities

Create a view called high_scores that holds reviews with scores above a 9.

CREATE VIEW high_scores AS SELECT * FROM REVIEWS WHERE score > 9;

a very basic way of finding out what qualifies for a key in an existing, populated table:

Count the distinct records for all possible combinations of columns. If the resulting number x equals the number of all rows in the table for a combination, you have discovered a superkey. Then remove one column after another until you can no longer remove columns without seeing the number x decrease. If that is the case, you have discovered a (candidate) key

Fill in the correct function call for conversion. The UK date formats are 3 and 103, representing two-digit year (dmy) and four-digit year (dmyyyy), respectively. The corresponding US date formats are 1 and 101.

DECLARE @CubsWinWorldSeries DATETIME2(3) = '2016-11-03 00:30:29.245' SELECT CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 0) AS DefaultForm CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 3) AS UK_dmy, CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 1) AS US_mdy, CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 103) AS UK_dmyyyy CONVERT(NVARCHAR(30), @CubsWinWorldSeries, 101) AS US_mdyyyy

Use the 'D' format parameter (this is case sensitive!) to build long dates. Also, fill in the culture for Indonesia, which in the .NET framework is id-ID.

DECLARE @Python3ReleaseDate DATETIME2(3) = '2008-12-03 19:45:00.033' SELECT -- Fill in the format parameter FORMAT(@Python3ReleaseDate, 'D', 'en-US') AS US_D, FORMAT(@Python3ReleaseDate, 'D', 'de-DE') AS DE_D, -- Fill in the locale for Indonesia FORMAT(@Python3ReleaseDate, 'D', 'id-ID') AS ID_D, FORMAT(@Python3ReleaseDate, 'D', 'zh-cn') AS CN_D;

Create an integer variable named counter. Assign the value 20 to this variable.

DECLARE @counter INT SET @counter = 20 SELECT @counter

Set the correct date format so that the variable @date1 is interpreted as a valid date.

DECLARE @date1 NVARCHAR(20) = '2018-30-12'; SET DATEFORMAT ydm; SELECT ISDATE(@date1) AS result;

Change the language, so that '30.03.2019' is considered a valid date. Select the name of the month.

DECLARE @date1 NVARCHAR(20) = '30.03.2019'; SET LANGUAGE Dutch; SELECT @date1 AS initial_date, ISDATE(@date1) AS is_valid, DATENAME(MONTH, @date1) AS month_name;

Using the DATENAME() function, fill in the appropriate function calls.

SELECT DATENAME(YEAR, @BerlinWallFalls) AS TheYear, DATENAME(MONTH, @BerlinWallFalls) AS TheMonth, DATENAME(DAY, @BerlinWallFalls) AS TheDay, DATENAME(DAYOFYEAR, @BerlinWallFalls) AS TheDayOfYear, -- Day of week is WEEKDAY DATENAME(WEEKDAY, @BerlinWallFalls) AS TheDayOfWeek, DATENAME(WEEK, @BerlinWallFalls) AS TheWeek, DATENAME(SECOND, @BerlinWallFalls) AS TheSecond, DATENAME(NANOSECOND, @BerlinWallFalls) AS TheNanosecond;

Create a new column, showing the cocoa percentage of the chocolate bar that received a lower score, with cocoa coming from the same location (broad_bean_origin is the same). Create a new column with the difference between the current bar's cocoa percentage and the percentage of the previous bar.

SELECT broad_bean_origin AS bean_origin, rating, cocoa_percent, LAG(cocoa_percent) OVER(PARTITION BY broad_bean_origin ORDER BY rating ) AS percent_lower_rating FROM ratings WHERE company = 'Fruition' ORDER BY broad_bean_origin, rating ASC;

Find the dates for fiscal week 29 of fiscal year 2019.

SELECT c.Date FROM dbo.Calendar c WHERE -- Instead of month, use the fiscal week c.FiscalWeekOfYear = 29 -- Instead of calendar year, use fiscal year AND c.FiscalYear = 2019 ORDER BY c.Date ASC;

Replace the character "&" from the company name with "and".

SELECT company AS initial_name, REPLACE(company, '&', 'and') AS new_name FROM ratings WHERE CHARINDEX('&', company) > 0;

Format the message so that company and broad_bean_origin are uppercase.

SELECT company, bean_type, broad_bean_origin, -- 'company' and 'broad_bean_origin' should be in uppercase 'The company ' + UPPER(company) + ' uses beans of type "' + bean_type + '", origina FROM ratings WHERE LOWER(broad_bean_origin) NOT LIKE '%unknown%' AND LOWER(bean_type) NOT LIKE '%unknown%';

Select 5 characters from the email address, starting with position 3.

SELECT email, SUBSTRING(email, 3, 5) AS some_letters FROM voters;

Calculate the number of years since a participant celebrated their 18th birthday until the first time they voted.

SELECT first_name, birthdate, first_vote_date, DATEDIFF(YEAR, DATEADD(YEAR, 18, birthdate), first_vote_date) AS adult_years_until_vo FROM voters

Add five days to the first_vote_date, to calculate the date when the vote was processed.

SELECT first_name, first_vote_date, DATEADD(DAY, 5, first_vote_date) AS processing_vote_date FROM voters;

Now that we have the data set prepared, let's make it more user-friendly. Perform an explicit conversion from datetime to varchar(10), to show the dates as yy/mm/dd.

SELECT first_name, last_name, CONVERT(varchar(10), birthdate, 11) AS birthdate, gender, country FROM voters WHERE country = 'Belgium' AND gender = 'F' AND total_votes > 20;

Extract the month number of the first vote. Extract the month name of the first vote. Extract the weekday number of the first vote. Extract the weekday name of the first vote.

SELECT first_name, last_name, DATEPART(MONTH, first_vote_date) AS first_vote_month1, DATENAME(MONTH, first_vote_date) AS first_vote_month2, DATEPART(WEEKDAY, first_vote_date) AS first_vote_weekday1, DATENAME(WEEKDAY, first_vote_date) AS first_vote_weekday2 FROM voters;

Select the year of the first vote. Select the month of the first vote date. Create a date as the start of the month of the first vote.

SELECT first_name, last_name, YEAR(first_vote_date) AS first_vote_year, MONTH(first_vote_date) AS first_vote_month, DATEFROMPARTS(YEAR(first_vote_date), MONTH(first_vote_date), 1) AS first_vote_startin FROM voters;

Restrict the query to show only the voters who started to vote after 2015.

SELECT first_name, last_name, YEAR(first_vote_date) AS first_vote_year, MONTH(first_vote_date) AS first_vote_month, DAY(first_vote_date) AS first_vote_day FROM voters WHERE YEAR(first_vote_date) > 2015;

Extract the year, month and day of the first vote.

SELECT first_name, last_name, YEAR(first_vote_date) AS first_vote_year, MONTH(first_vote_date) AS first_vote_month, DAY(first_vote_date) AS first_vote_day FROM voters;

Select the voters whose first name contains an "a" followed by other letters, then a "w", followed by other letters.

SELECT first_name, last_name, email FROM voters WHERE PATINDEX('%a%w%', first_name) > 0;

Write a query to select the voters whose first name contains the letters "rr".

SELECT first_name, last_name, email FROM voters WHERE PATINDEX('%rr%', first_name) > 0;

Write a query to select the voters whose first name starts with "C" and has "r" as the third letter.

SELECT first_name, last_name, email FROM voters WHERE PATINDEX('C_r%', first_name) > 0;

Round down the ratings to the nearest integer value.

SELECT rating, CEILING(rating) AS round_up, floor(rating) AS round_down FROM ratings;

this gives a result of 12

SELECT (4 * 3);

create a derived table to return all patient records with the highest BloodPressure at their Age level.```

SELECT * FROM Kidney a JOIN (SELECT Age, MAX(BloodPressure) AS MaxBloodPressure FROM Ki ON a.BloodPressure = b.MaxBloodPressure AND a.Age = b.Age

to return all rows with non-missing SHAPE values, you can use:

SELECT * FROM Incidents WHERE Shape IS NOT NULL

DELETE the record from album where album_id is 1 and then hit 'Submit Answer'.

SELECT * FROM album DELETE FROM album WHERE album_id = 1

Query the information schema to get views. Exclude system views in the results.

SELECT * FROM information_schema.views WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

Write a query that returns a list of orders where the standard_qty is zero and either the gloss_qty or poster_qty is over 1000.

SELECT * FROM orders WHERE standard_qty = 0 AND (gloss_qty > 1000 OR poster_qty > 1000);

Write a query that returns all the orders where the standard_qty is over 1000, the poster_qty is 0, and the gloss_qty is 0.

SELECT * FROM orders WHERE standard_qty > 1000 AND poster_qty = 0 AND gloss_qty = 0;

If you only want to return a certain number of results, you can use the LIMIT keyword to limit the number of rows returned:

SELECT * FROM people LIMIT 10;

Sometimes, you may want to select all columns from a table. Typing out every column name would be a pain, so there's a handy shortcut:

SELECT * FROM people;

Use the web_events table to find all information regarding individuals who were contacted via the organic or adwords channels, and started their account at any point in 2016, sorted from newest to oldest.

SELECT * FROM web_events WHERE channel IN ('organic', 'adwords') AND occurred_at BETWEEN '2016-01-01' AND '2017-01-01' ORDER BY occurred_at DESC;

Use the web_events table to find all information regarding individuals who were contacted via the channel of organic or adwords.

SELECT * FROM web_events WHERE channel IN ('organic', 'adwords');

quick recap on how joins generally work:

SELECT ... FROM table_a JOIN table_b ON ... WHERE ...

Now group the result by organization sector, professor, and university city. Count the resulting number of rows.

SELECT COUNT(*), organizations.organization_sector, professors.id, universities.university_city FROM affiliations JOIN professors ON affiliations.professor_id = professors.id JOIN organizations ON affiliations.organization_id = organizations.id JOIN universities ON professors.university_id = universities.id GROUP BY organizations.organization_sector, professors.id, universities.university_city;

Count the number of total affiliations by university. Sort the result by that count, in descending order.

SELECT COUNT(*), professors.university_id FROM affiliations JOIN professors ON affiliations.professor_id = professors.id -- Group by the university ids of professors GROUP BY professors.university_id ORDER BY count DESC;

the number of distinct birth dates contained in the people table:

SELECT COUNT(DISTINCT birthdate) FROM people;

identify the candidate key by trying out different combination of columns.

SELECT COUNT(DISTINCT(firstname, lastname)) FROM professors;

to count the number of birth dates present in the people table:

SELECT COUNT(birthdate) FROM people;

Write a SQL query to truncate the values in the Cost column to the nearest whole number.

SELECT Cost, round(cost, 0, 1) AS TruncateCost FROM Shipments

Write a SQL query to round the values in the Cost column to the nearest whole number.

SELECT Cost, ROUND(Cost, 0) AS RoundedCost FROM Shipments

Write a T-SQL query which only returns rows where IncidentState is missing. Replace all the missing values in the IncidentState column with the values in the City column and name this new column Location.

SELECT IncidentState, ISNULL(IncidentState, City) AS Location FROM Incidents WHERE IncidentState IS NULL

Write a T-SQL query which will return the sum of the Quantity column as Total for each type of MixDesc.

SELECT MixDesc, SUM(Quantity) AS Total FROM Shipments GROUP BY MixDesc

Create a query that returns the number of rows for each type of MixDesc.

SELECT MixDesc, count(*) FROM Shipments GROUP BY MixDesc

Write a query that returns the approximate delivery date as five days after the ShipDate.

SELECT OrderDate, DATEADD(DD, 5, ShipDate) AS DeliveryDate FROM Shipments

adding up the numeric values in a column:

SELECT SUM(budget) FROM films;

Write a T-SQL query to calculate the average, minimum, and maximum values of the DurationSeconds column grouped by Shape. You need to select an additional column. What is it?

SELECT Shape, AVG(DurationSeconds) AS Average, MIN(DurationSeconds) AS Minimum, MAX(DurationSeconds) AS Maximum FROM Incidents GROUP BY Shape

Calculate the length of each broad_bean_origin. Order the results from the longest to shortest.

SELECT TOP 10 company, broad_bean_origin, LEN(broad_bean_origin) AS length FROM ratings ORDER BY Length DESC;

Make the first selection from the album table. Then join the results by providing the relevant keyword and selecting from the artist table.

SELECT album_id AS ID, title AS description, 'Album' AS Source -- Complete the FROM statement FROM album -- Combine the result set using UNION SELECT artist_id AS ID, name AS description, 'Artist' AS Source -- Complete the FROM statement FROM artist;

Find the dates of all Tuesdays in December covering the calendar years 2008 through 2010.

SELECT c.Date FROM dbo.Calendar c WHERE c.MonthName = 'December' AND c.DayName = 'Tuesday' AND c.CalendarYear BETWEEN 2008 AND 2010 ORDER BY c.Date;

Select the company, bean origin and the rating from the ratings table. The rating should be converted to a whole number.

SELECT company, bean_origin, CONVERT(int, rating) AS rating FROM ratings;

Select the company, bean origin and the rating from the ratings table where the whole part of the rating equals 3.

SELECT company, bean_origin, rating FROM ratings WHERE CONVERT(int, rating) = 3;

Have a look at the existing foreign key constraints by querying table_constraints in information_schema.

SELECT constraint_name, table_name, constraint_type FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY';

Create a column that divides the standard_amt_usd by the standard_qty to find the unit price for standard paper for each order. Limit the results to the first 10 orders, and include the id and account_id fields.

SELECT id, account_id, standard_amt_usd/standard_qty AS unit_price FROM orders LIMIT 10;

Get all details for Spanish language films released after 2000, but before 2010.

Select * from films where release_year > 2000 AND release_year < 2010 AND language = 'Spanish';

Get the average amount grossed by all films whose titles start with the letter 'A'.

Select avg(gross) from films where title like 'A%';

Create a CTE BloodPressure that returns one column (MaxBloodPressure) which contains the maximum BloodPressure in the table. Join this CTE (using an alias b) to the main table (Kidney) to return information about patients with the maximum BloodPressure.

WITH BloodPressure (MaxBloodPressure) AS (SELECT MAX(BloodPressure) AS MaxBloodPressure FROM Kidney) SELECT * FROM Kidney a JOIN BloodPressure b ON a.BloodPressure = b.MaxBloodPressure

Create a CTE ModePrice that returns two columns (OrderPrice and UnitPriceFrequency). Write a query that returns all rows in this CTE.

WITH ModePrice (OrderPrice, UnitPriceFrequency) AS ( SELECT OrderPrice, ROW_NUMBER() OVER(PARTITION BY OrderPrice ORDER BY OrderPrice) AS UnitPri FROM Orders ) SELECT * FROM ModePrice

POWER(number, power)

raises the number to the specified power

Use the SUM() function to get the total duration of all films.

select sum(duration) from films;

Use the SUM() function to get the total amount grossed by all films.

select sum(gross) from films;

%

tells us that we might want any number of characters leading up to a particular set of characters or following a certain set of characters, as we saw with the google syntax above

when you truncate

the number is neither rounded up or down but remains a whole number.

change the existing query so that only the first 50 rows are returned.

top (50) points

Create one integer column called track_length_mins.

track_length_mins INT, );

There are essentially three ideas that are aimed at database normalization:

1.Are the tables storing logical groupings of the data? 2.Can I make changes in a single location, rather than in many tables for the same information? 3.Can I access and manipulate data quickly and efficiently?

2 hours in sql

120; (it is 60 times 2)

Alter professors to add the text column university_shortname

ALTER TABLE professors ADD COLUMN university_shortname text; SELECT * FROM professors

Rename the university_shortname column to university_id in professors.

ALTER TABLE professors Rename column university_shortname to university_id;

Add a foreign key on university_id column in professors that references the id column in universities. Name this foreign key professors_fkey.

ALTER TABLE professors ADD CONSTRAINT professors_fkey FOREIGN KEY (university_id) REFERERENCES universities (id) ;

Add a not-null constraint for the firstname column

ALTER TABLE professors ALTER COLUMN firstname SET NOT NULL;

If you want to add a unique constraint to an existing table, you do it like that:

ALTER TABLE table_name ADD CONSTRAINT some_name UNIQUE(column_name);

The syntax for adding a new column in a table is the following:

ALTER TABLE table_name ADD column_name data_type

To delete columns:

ALTER TABLE table_name DROP COLUMN column_name;

To rename columns:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Add a new column called last_vote_time, to keep track of the most recent time when a person voted ("16:55:00").

ALTER TABLE voters ADD last_vote_time time;

Add a new column,last_login, storing the most recent time a person accessed the application ("2019-02-02 13:44:00").

ALTER TABLE voters ADD last_login datetime2;

Add a new column with the correct data type, for storing the last date a person voted ("2018-01-17").

ALTER TABLE voters ADD last_vote_date date;

Add a unique constraint to the university_shortname column in universities. Give it the name university_shortname_unq.

ALTER table universities ADD constraint university_shortname_unq UNIQUE(university_shortname);

Add a not-null constraint for the lastname column.

ALter table professors alter column lastname set not NULL;

restrict the query to only return films that took in more than $2M gross.

AND gross > 2000000;

Sometimes in your database development, you may need to round the results of a calculation. There are three functions you can use for this:

CEILING(expression): rounds-up to the nearest integer value FLOOR(expression): rounds-down to the nearest integer value ROUND(expression, length): rounds the expression to the specified length.

If you need to check whether an expression exists within a string, you can use the

CHARINDEX() function. This function returns the position of the expression you are searching within the string. The syntax is: CHARINDEX(expression_to_find, expression_to_search [, start_location])

Create a table named 'results' with 3 VARCHAR columns called track, artist, and album, with lengths 200, 120, and 160, respectively.

Create the table CREATE TABLE results ( -- Create track column track VARCHAR(200), -- Create artist column artist VARCHAR(120), -- Create album column album VARCHAR(160), );

When we talk about 'CRUD' operations on the records of a database, what do we mean - what do those letters stand for?

Create, Read, Update, Delete.

Raise the number stored in the @number variable to the power from the @power variable. Calculate the square of the @number variable (square means the power of 2). Calculate the square root of the number stored in the @number variable.

DECLARE @number DECIMAL(4, 2) = 4.5; DECLARE @power INT = 4; SELECT @number AS number, @power AS power, POWER(@number, @power) AS number_to_power, SQUARE(@number) num_squared, SQRT(@number) num_square_root;

Split the phrase declared in the variable @phrase into individual words.

DECLARE @phrase NVARCHAR(MAX) = 'In the morning I brush my teeth. In the afternoon I tak SELECT value FROM STRING_SPLIT(@phrase, ' ');

Using the DATEPART() function, fill in the appropriate date parts. For a list of parts, review https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql

SELECT DATEPART(YEAR, @BerlinWallFalls) AS TheYear, DATEPART(MONTH, @BerlinWallFalls) AS TheMonth, DATEPART(DAY, @BerlinWallFalls) AS TheDay, DATEPART(DAYOFYEAR, @BerlinWallFalls) AS TheDayOfYear, -- Day of week is WEEKDAY DATEPART(WEEKDAY, @BerlinWallFalls) AS TheDayOfWeek, DATEPART(WEEK, @BerlinWallFalls) AS TheWeek, DATEPART(SECOND, @BerlinWallFalls) AS TheSecond, DATEPART(NANOSECOND, @BerlinWallFalls) AS TheNanosecond;

Retrieve the first 25 characters from the description column in the grid table. Name the results first_25_left.

SELECT LEFT(description, 25) AS first _25_left FROM grid;

Create a list with all the values found in the bean_origin column for the companies: 'Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters'. The values should be separated by commas (,).

SELECT STRING_AGG(bean_origin, ',') AS bean_origins FROM ratings WHERE company IN ('Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters');

Select the album_id and title columns from album (the main source table name). Select the name column from artist and alias it as artist. Identify a common column between the album and artist tables and perform an inner join.

SELECT album_id, title, name AS artist FROM album INNER JOIN artist on album.artist_id =artist.artist_id;

Remove the string "(Valrhona)" from the company name "La Maison du Chocolat (Valrhona)".

SELECT company AS old_company, REPLACE(company, '(Valrhona)', '') AS new_company, bean_type, broad_bean_origin FROM ratings WHERE company = 'La Maison du Chocolat (Valrhona)';

Calculate the minimum rating received by each company.

SELECT company, AVG(cocoa_percent) AS avg_cocoa, MIN(rating) AS min_rating FROM ratings GROUP BY company;

Use an aggregate function to order the results of the query by the maximum rating, in descending order.

SELECT company, AVG(cocoa_percent) AS avg_cocoa, MIN(rating) AS min_rating, MAX(rating) AS max_rating FROM ratings GROUP BY company ORDER BY MAX(rating) DESC;

Select the number of cocoa flavors the company was rated on. Select the lowest, highest and the average rating that each company received.

SELECT company, COUNT(*) AS flavors, MIN(rating) AS lowest_score, MAX(rating) AS highest_score, AVG(rating) AS avg_score FROM ratings GROUP BY company ORDER BY flavors DESC;

Round the average rating to 1 decimal and show it as a different column.

SELECT company, COUNT(*) AS flavors, MIN(rating) AS lowest_score, MAX(rating) AS highest_score, AVG(rating) AS avg_score, ROUND(AVG(rating), 1) AS round_avg_score FROM ratings GROUP BY company ORDER BY flavors DESC;

Create a list with the values found in the bean_origin column for each of the companies: 'Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters'. The values should be separated by commas (,).

SELECT company, STRING_AGG(bean_origin, ',') AS bean_origins FROM ratings WHERE company IN ('Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters') GROUP BY company;

Arrange the values from the list in alphabetical order.

SELECT company, STRING_AGG(bean_origin, ',') WITHIN GROUP (ORDER BY bean_origin) AS bean_origins FROM ratings WHERE company IN ('Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters') GROUP BY company;

Select information from the ratings table, excluding the unknown broad_bean_origins. Convert the broad_bean_origins to lowercase when comparing it to the '%unknown%' expression.

SELECT company, bean_type, broad_bean_origin, 'The company ' + company + ' uses beans of type "' + bean_type + '", originating fr FROM ratings WHERE LOWER(broad_bean_origin) NOT LIKE '%unknown%';

Use the CONVERT() function to translate DateText into a date data type. Then use the CONVERT() function to translate DateText into a DATETIME2(7) data type.

SELECT d.DateText AS String, -- Convert to DATE CONVERT(DATE, d.DateText) AS StringAsDate, -- Convert to DATETIME2(7) CONVERT(DATETIME2(7), d.DateText) AS StringAsDateTime2 FROM dbo.Dates d;

Retrieve the date when each voter had their 18th birthday.

SELECT first_name, birthdate, DATEADD(YEAR, 18, birthdate) AS eighteenth_birthday FROM voters;

Select only the voters whose first name has fewer than 5 characters and email address meets these conditions in the same time: (1) starts with the letter "j", (2) the third letter is "a" and (3) is created at yahoo.com.

SELECT first_name, last_name, birthdate, email, country FROM voters WHERE LEN(first_name) < 5 AND PATINDEX('j_a%@yahoo.com', email) > 0;

Select information from the voters table, including a new column called part1, containing only the first 3 letters from the first name.

SELECT first_name, last_name, country, LEFT(first_name, 3) AS part1 FROM voters;

Add a new column to the previous query, containing the last 3 letters from the last name.

SELECT first_name, last_name, country, LEFT(first_name, 3) AS part1, RIGHT(last_name, 3) AS part2 FROM voters;

Restrict the query to select the voters with "dan" in the first_name and DO NOT have the letter "z" in the last_name.

SELECT first_name, last_name, email FROM voters WHERE CHARINDEX('dan', first_name) > 0 AND CHARINDEX ('z', last_name) = 0;

Restrict the query to select the voters with "dan" in the first_name and "z" in the last_name.

SELECT first_name, last_name, email FROM voters WHERE CHARINDEX('dan', first_name) > 0 AND CHARINDEX ('z', last_name) > 0;

Restrict the query to select only the voters whose first name contains the expression "dan"

SELECT first_name, last_name, email FROM voters WHERE CHARINDEX('dan', first_name) > 0;

Write a query to select the voters whose first name contains one of these letters: "x", "w" or "q"

SELECT first_name, last_name, email FROM voters WHERE PATINDEX('%[xwq]%', first_name) > 0;

Add a new column in the query in which you replace the "yahoo.com" in all email addresses with "live.com".

SELECT first_name, last_name, email, REPLACE(email, 'yahoo.com', 'live.com') AS new_email FROM voters;

Select information from the voters table, including the day of the year when they first voted.

SELECT first_name, last_name, first_vote_date, DATENAME(DAYOFYEAR, first_vote_date) AS first_vote_dayofyear FROM voters;

Select information from the voters table, including the name of the month when they first voted.

SELECT first_name, last_name, first_vote_date, DATENAME(MONTH, first_vote_date) AS first_vote_month FROM voters;

Select information from the voters table, including the day of the week when they first voted.

SELECT first_name, last_name, first_vote_date, DATENAME(weekday, first_vote_date) AS first_vote_dayofweek FROM voters;

the following gives a result of 1

SELECT (4 / 3);

Join all tables in the database (starting with affiliations, professors, organizations, and universities) and look at the result.

SELECT * FROM affiliations JOIN professors ON affiliations.professor_id = professors.id JOIN organizations ON affiliations.organization_id = organizations.id JOIN universities ON professors.university_id = universities.id;

Use the web_events table to find all information regarding individuals who were contacted via any method except using organic or adwords methods.

SELECT * FROM web_events WHERE channel NOT IN ('organic', 'adwords');

Write a query that will show a message like the following, for each voter: Carol Rai was born in 1989.

SELECT -- Transform the year part from the birthdate to a string first_name + ' ' + last_name + ' was born in ' + CAST(YEAR(birthdate) AS nvarchar) + FROM voters;

Divide the total votes by 5.5. Transform the result to an integer.

SELECT -- Transform to int the division of total_votes to 5.5 CAST(total_votes/5.5 AS int) AS DividedVotes FROM voters;

Increment the variable counter by 1 and assign it back to counter.

SELECT @counter = @counter + 1

Write a T-SQL query which will return the average, minimum, and maximum values of the DurationSeconds column.

SELECT AVG(DurationSeconds) AS Average, MIN(DurationSeconds) AS Minimum, MAX(DurationSeconds) AS Maximum FROM Incidents

Write a T-SQL query which will return the average, minimum, and maximum values of the DurationSeconds column.

SELECT AVG(DurationSeconds) AS Average, MIN(DurationSeconds) AS Minimum, MAX(DurationSeconds) AS Maximum FROM Incidents

average value from the budget column of the films table.

SELECT AVG(budget) FROM films;

casts are a possible solution for data type issues. If you know that a certain column stores numbers as text, you can cast the column to a numeric form, i.e. to integer.

SELECT CAST(some_column AS integer) FROM table;

For example, this code gives the number of rows in the people table:

SELECT COUNT(*) FROM people;

Create a new column, SourceCountry, defined from these cases:When Country is 'us' then it takes the value 'USA'.Otherwise it takes the value 'International'.

SELECT Country, CASE WHEN Country = 'us' THEN 'USA' ELSE 'International' END AS SourceCountry FROM Incidents

Replace missing values in Country with the first non-missing value from IncidentState or City, in that order. Name the new column Location.

SELECT Country, COALESCE(Country, incidentstate, city) AS Location FROM Incidents WHERE Country IS NULL

Suppose you want to calculate the number of years between two different dates, DateOne and DateTwo. Which SQL statement would you use to perform that calculation?

SELECT DATEDIFF(YYYY, DateOne, DateTwo)

Write a query that returns the number of days between OrderDate and ShipDate.

SELECT OrderDate, ShipDate, DATEDIFF(DD, OrderDate, ShipDate) AS Duration FROM Shipments

Count the number of rows in each partition. Partition the table by TerritoryName.

SELECT OrderID, TerritoryName, COUNT(*) OVER(PARTITION BY TerritoryName) AS TotalOrders FROM Orders

Write a T-SQL query that returns the sum of OrderPrice by creating partitions for each TerritoryName.

SELECT OrderID, TerritoryName, SUM(OrderPrice) OVER(PARTITION BY TerritoryName) AS TotalPrice FROM Orders

Create dates from component parts in the calendar table: calendar year, calendar month, and the day of the month.

SELECT TOP(10) DATEFROMPARTS(c.CalendarYear, c.CalendarMonth, c.Day) AS Cal FROM dbo.Calendar c WHERE c.CalendarYear = 2017 ORDER BY c.FiscalDayOfYear ASC;

Write a T-SQL query that returns the first OrderDate by creating partitions for each TerritoryName.

SELECT TerritoryName, OrderDate, FIRST_VALUE(OrderDate) OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS Firstorder FROM Orders

Write a T-SQL query that for each territory:Shifts the values in OrderDate one row down. Call this column PreviousOrder.Shifts the values in OrderDate one row up. Call this column NextOrder. You will need to PARTITION BY the territory Hint

SELECT TerritoryName, OrderDate, LAG(OrderDate) OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS Pr LEAD(OrderDate) OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS Ne FROM Orders

Write a T-SQL query that assigns row numbers to all records partitioned by TerritoryName and ordered by OrderDate.

SELECT TerritoryName, OrderDate, ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS Ordercount FROM Orders

Create the window, partition by TerritoryName and order by OrderDate to calculate a running total of OrderPrice.

SELECT TerritoryName, OrderDate, SUM(OrderPrice) OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS Te FROM Orders

Write a query that calculates the square and square root of the WeightValue column.

SELECT WeightValue, SQUARE(WeightValue) AS WeightSquare, SQRT(WeightValue) AS WeightSqrt FROM Shipments

find out how many unique values there are in the university_city column.

SELECT count(distinct(university_city)) FROM universities;

Write a query that finds the percentage of revenue that comes from poster paper for each order. You will need to use only the columns that end with _usd. (Try to do this without using the total column.) Display the id and account_id fields also. NOTE - you will receive an error with the correct solution to this question. This occurs because at least one of the values in the data creates a division by zero in your formula. You will learn later in the course how to fully handle this issue. For now, you can just limit your calculations to the first 10 orders, as we did in question #1, and you'll avoid that set of data that causes the problem.

SELECT id, account_id, poster_amt_usd/(standard_amt_usd + gloss_amt_usd + poster_amt_usd) AS post_per FROM orders LIMIT 10;

Using the accounts table, find all the companies whose names do not start with 'C' and end with 's'.

SELECT name FROM accounts WHERE name NOT LIKE 'C%' AND name LIKE '%s';

Use the accounts table to find: All the companies whose names do not start with 'C'. All companies whose names do not contain the string 'one' somewhere in the name. All companies whose names do not end with 's'.

SELECT name FROM accounts WHERE name NOT LIKE 'C%'; SELECT name FROM accounts WHERE name NOT LIKE '%one%'; SELECT name FROM accounts WHERE name NOT LIKE '%s';

the following query matches companies like 'DataCamp', 'DataComp', and so on:

SELECT name FROM companies WHERE name LIKE 'DataC_mp';

to get all the names in the people table, in reverse alphabetical order:

SELECT name FROM people ORDER BY name DESC;

names of all people whose birth dates are not missing in the people table

SELECT name FROM people WHERE birthdate IS NOT NULL;

Use the accounts table to find the account name, primary_poc, and sales_rep_id for Walmart, Target, and Nordstrom.

SELECT name, primary_poc, sales_rep_id FROM accounts WHERE name IN ('Walmart', 'Target', 'Nordstrom');

Use the accounts table to find the account name, primary_poc, and sales rep id for all stores except Walmart, Target, and Nordstrom.

SELECT name, primary_poc, sales_rep_id FROM accounts WHERE name NOT IN ('Walmart', 'Target', 'Nordstrom');

When you use the BETWEEN operator in SQL, do the results include the values of your endpoints, or not? Figure out the answer to this important question by writing a query that displays the order date and gloss_qty data for all orders where gloss_qty is between 24 and 29. Then look at your output to see if the BETWEEN operator included the begin and end values or not.

SELECT occurred_at, gloss_qty FROM orders WHERE gloss_qty BETWEENSELECT occurred_at, gloss_qty FROM orders WHERE gloss_qty BETWEEN 24 AND 29; You should notice that there are a number of rows in the output of this query where thegloss_qtyvalues are 24 or 29. So the answer to the question is that yes, the BETWEEN operator in SQL is inclusive; that is, the endpoint values are included. So the BETWEEN statement in this query is equivalent to having written "WHERE gloss_qty >= 24 AND gloss_qty <= 29."24 AND 29;

JOIN professors with universities on professors.university_id = universities.id, i.e., retain all records where the foreign key of professors is equal to the primary key of universities. Filter for university_city = 'Zurich'.

SELECT professors.lastname, universities.id, universities.univer FROM professors JOIN universities ON professors.university_id = universities.id WHERE universities.university_city = 'Zurich';

titles of all films which were filmed in China:

SELECT title FROM films WHERE country = 'China';

When combining AND and OR, be sure to enclose the individual clauses in parentheses

SELECT title FROM films WHERE (release_year = 1994 OR release_year = 1995) AND (certification = 'PG' OR certification = 'R');

Get the release year and largest budget for all films, grouped by release year.

Select release_year, MAX(budget) from films GROUP BY release_year;

GROUP BY

allows you to group a result by one or more columns, like so: SELECT sex, count(*) FROM employees GROUP BY sex;

_

any single character

[]

any single character within the range specified in brackets

%

any string of zero or more characters

JOINs

are useful for allowing us to pull data from multiple tables. the SELECT clause indicates which column(s) of data you'd like to see in the output (For Example, orders.* gives us all the columns in orders table in the output). The FROM clause indicates the first table from which we're pulling data, and the JOIN indicates the second table. The ON clause specifies the column on which you'd like to merge the two tables together.

In SQL Server, you can use the LEN() function for

calculating the length of a string of characters.

Delete the university_professors table.

drop table university_professors;

event date in descending order

event_date DESC;

LIKE operator can be used

in a WHERE clause to search for a pattern in a column. To accomplish this, you use something called a wildcard as a placeholder for some other values. There are two wildcards you can use with LIKE: The % wildcard will match zero, one, or many characters in text.

*

is a shortcut for selecting all columns from a table.

null

missing value

when you're dividing make sure at least

one of your numbers has a decimal place: SELECT 45 * 100.0 / 10;

IN

operator allows you to specify multiple values in a WHERE clause, making it easier and quicker to specify multiple OR conditions!

You can also use ISNULL() to

replace values from a different column instead of a specified word.

Get the number of decades the films table covers. Alias the result as number_of_decades. The top half of your fraction should be enclosed in parentheses.

select (max(release_year) - min(release_year)) / 10.0 AS number_of_decades from films;

Write a T-SQL query which returns only the IncidentDateTime and IncidentState columns where IncidentState is not missing.

select * FROM Incidents WHERE IncidentState IS NOT NULL

Use a shortcut to amend the current query, returning ALL columns in the table.

select * from eurovision;

Get all details for all films with an R certification.

select * from films where certification = 'R';

Get all details for all French language films.

select * from films where language = 'french'

Get all details for all films except those released in 2015 and order them by duration.

select * from films where release_year <> 2015 order by duration;

Write a query that returns all the columns and 10 rows from professors.

select * from professors limit 10;

Get the IMDB score and film ID for every film from the reviews table, sorted from highest to lowest score.

select IMDB_score, film_id from reviews table order by imdb_score DESC;

Get the average duration of all films.

select avg(duration) from films;

Get the average duration in hours for all films, aliased as avg_duration_hours

select avg(duration) / 60.0 AS avg_duration_hours from films;

Get the average amount grossed by all films

select avg(gross) from films;

Get the birth date and name of people in the people table, in order of when they were born and alphabetically by name.

select birthdate, name from people order by birthdate, name;

Get the birth date and name for every person, in order of when they were born.

select birthdate, name from people order by birthdate;

Get the number of Hindi language films.

select count(*) from films where language = Hindi

Get the number of films which don't have a language associated with them.

select count(*) from films where language is null;

Get the number of films released before 2000.

select count(*) from films where release_year < 2000

Count the number of rows in the people table.

select count(*) from people;

Get the percentage of people who are no longer alive. Alias the result as percentage_dead. Remember to use 100.0 and not 100!

select count(deathdate) * 100.0 / count (*) AS percentage_dead from people;

Get the duration of the longest film.

select max(duration) from films;

Get the amount grossed by the best performing film between 2000 and 2012, inclusive.

select max(gross) from films where release_year >= 2000 AND release_year <= 2012;

Get the amount grossed by the best performing film.

select max(gross) from films;

Get the number of years between the newest film and oldest film. Alias the result as difference.

select max(release_year) - min(release_year) AS difference from films;

Get the duration of the shortest film.

select min(duration) from films;

Get the amount grossed by the worst performing film in 1994.

select min(gross) from films where release_year = 1994;

Get the amount grossed by the worst performing film.

select min(gross) from films;

Get the names of people, sorted by birth date

select name from people order by birthdate;

Get the names of people who are still alive, i.e. whose death date is missing.

select name from people where deathdate is null;

Get the names of all people whose names begin with 'B'. The pattern you need is 'B%'.

select name from people where name like 'B%';

Get the names of people whose names don't start with A. The pattern you need is 'A%'

select name from people where name not like 'A%';

Get the names of people whose names have 'r' as the second letter. The pattern you need is '_r%'

select name from people where name like '_r%';

Get the names of people from the people table, sorted alphabetically.

select name from people order by name;

Get the name and birth date of the person born on November 11th, 1974. Remember to use ISO date format ('1974-11-11')!

select name, birthdate from people where birthdate = '1974-11-11'

Get the release year and count of films released in each year.

select release_year, count(*) from films GROUP BY release_year;

Get the release year, country, and highest budget spent making a film for each year, for each country. Sort your results by release year and country.

select release_year, country, max(budget) from films group by release_year, country order by release_year, country

Use the SUM() function to get the total amount grossed by all films made in the year 2000 or later.

select sum(gross) from films where release_year >= 2000;

Get the title of every film which doesn't have a budget associated with it.

select title from film where budget is null;

Get the title of films released in 2000 or 2012, in the order they were released.

select title from films where release_year = 2000 OR release_year = 2012 order by release_year;

Get the title and certification of all films with an NC-17 or R certification.

select title, certification from films where certification IN ('NC-17', 'R');

Get the title and duration for every film, in order of longest duration to shortest.

select title, duration from films order by duration DESC;

Get the title and duration in hours for all films. The duration is in minutes, so you'll need to divide by 60.0 to get the duration in hours. Alias the duration in hours as duration_hours.

select title, duration / 60.0 AS duration_hours from films

Get the title and gross earnings for movies which begin with the letter 'M' and order the results alphabetically.

select title, gross from films where title like "M%' order by title;

Get the title and net profit (the amount a film grossed, minus its budget) for all films. Alias the net profit as net_profit.

select title, gross - budget AS net_profit from films;

Get the title and release year for all Spanish language films released before 2000.

select title, release_year from films where release_year < 2000 language = 'spanish';

Get the title and release year for films released in the 90s.

select title, release_year from films where release_year >= 1990 AND release_year < 2000

Get the title and release year of all films released in 1990 or 2000 that were longer than two hours. Remember, duration is in minutes!

select title, release_year from films where release_year IN (1990, 2000) AND duration > 120;

Get the title and release year of films released after 2000.

select title,release_year from films where release_year > 2000

return only half the rows using 'TOP', using the same shortcut as before to return all columns.

select top (50) percent * from eurovision

Qualify the name column by specifying the correct table prefix in both cases. Complete both INNER JOIN clauses to join album with track, and artist with album.

select track_id, track.name AS track_name, title as album_title, artist.name AS artist_name FROM track INNER JOIN album on track .album_id = album.album_id INNER JOIN artist on album .artist_id = artist_id;

Get all details for all films released in 2016.

select x from films where release_year = 2016

in PostgreSQL (the version of SQL we're using), you must use

single quotes with WHERE

If you want to search for a pattern in a string, PATINDEX() is the function you are looking for. This function returns

the starting position of the first occurrence of the pattern within the string. The syntax is: PATINDEX('%pattern%', expression)

Aliasing simply means

you assign a temporary name to something. To alias, you use the AS keyword