Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL > SQL Commands > Flashcards

SQL Commands Flashcards

1
Q

SELECT ALL

A

SELECT * FROM abc_table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

CREATE A TABLE FROM SCRATCH

A 
CREATE TABLE favoritebooks(id INTEGER PRIMARY KEY, name TEXT, rating INTEGER);
INSERT INTO favoritebooks VALUES 
(1, "Brown Bear", 10),
(2, "If You Give A Mouse A Cookie" , 7),
(3, "The Hungry Caterpillar" , 9)
;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

INSERT INTO favoritebooks _____? WHAT IS MISSING

A

INSERT INTO favoritebooks VALUES

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Find Movie of 2000 or higher and sort by ASC release_year CREATE TABLE movies (id INTEGER PRIMARY KEY, name TEXT, release_year INTEGER);
INSERT INTO movies VALUES (1, “Avatar”, 2009);
INSERT INTO movies VALUES (2, “Titanic”, 1997);
INSERT INTO movies VALUES (3, “Star Wars: Episode IV - A New Hope”, 1977);
INSERT INTO movies VALUES (4, “Shrek 2”, 2004);
INSERT INTO movies VALUES (5, “The Lion King”, 1994);
INSERT INTO movies VALUES (6, “Disney’s Up”, 2009);
SELECT * FROM movies;

A

SELECT * FROM movies
WHERE release_year >= 2000
ORDER BY release_year ASC;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Insert data into table lazy way

A

INSERT INTO active_wear VALUES
(1, “leggings”, “black”, “medium”, “Adidas”, 15, 59.99),
(2, “jacket”, “pink”, “small”, “Adidas”, 25, 59.99),

(3, “SOCKS”, “BLACK AND WHITE”, “unisex”, “Nike”, 50, 12.99),
(4, “Women T-shirt”, “White”, “large”, “Adidas”, 250, 35.99),

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

HOW MANY FROM A BRAND?

A 
SELECt COUNT(brand) FROM active_store
WHERE brand = "Nike";
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Max product quantity

A

SELECT MAX(quantity), item FROM active_store;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Min product

A

SELECT MIN(price), item FROM active_store;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

There is not BY after where

A

There is not BY after where

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Order by price DESC - from high to lowest

A

SELECT * FROM store

ORDER BY price desc;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Order price by ASC price

A

SELECT * FROM store

ORDER BY price desc;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Select all columns and rows from the customers table where the value in the age column is greater than 21 and thevalue in the state column is ‘PA’
hint there is not BY after Where and , after AND (served on next line)

A

SELECT *FROM customers
WHERE age > 21
AND state = ‘PA’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Select all columns and rows from the customers table where the value in the plan column is “free” or “basic”

A

SELECT *FROM customers

WHERE plan IN (“free”, “basic”)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How to use WHERE and INCLUDING in conditional statement

A

SELECT *FROM customers

WHERE plan IN (“free”, “basic”)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Select all columns and rows from the customers table where the value in the age column is greater than 21, and order the results by age starting with the highest value and DESC down

A

SELECT *FROM customers
WHERE age > 21
ORDER BY age DESC

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

ORDER IS FOLLOWED BY BY

A

ORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Select the gender column and the number of rows in the students table, and group by the value of the gender column

A

SELECT gender, COUNT(*)FROM students

GROUP BY gender

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Create a column called “type” which assigns whether someone is an “adult” or “minor” based on their age

A

SELECT name, CASE WHEN age > 18 THEN “adult”
ELSE “minor” END “type”
FROM customers

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Create a column called “flag” which assigns a 1 if someone’s tenure is greater than 5 years

A

SELECT name, CASE WHEN sum(tenure) > 5
THEN 1ELSE 0 END “flag”
FROM customers

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Select only the max age from the customers table

A

SELECT MAX(age)FROM customers

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Join the customers table and orders table based on customer ID to select all instances of “name” from the customers table and show then associated “item” from the orders table.

A

SELECT customers.name, orders.item
FROM customers
LEFT JOIN ordersON customers.id = orders.customer_id

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

This will show you the names of all the columns in a table

A

HELP TABLE database.table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

This will give you a sample of 20 rows from every column in the table

A

SELECT * FROM database.table LIMIT 20or

or

SELECT TOP 20 * FROM database.table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

SET ID TO autoincrement

A 
CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,
    heart_rate INTEGER);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES (“biking”, 30, 100, 110);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES (“biking”, 10, 30, 105);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES (“dancing”, 15, 200, 120);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Select all in tables which activities you have done that burns more that 50 calories and order by calories burned

A

SELECT * FROM exercise_logs WHERE calories > 50 ORDER BY calories;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

AND operator

FIND ACTIVITIES WHERE CALORIE BURN IS MORE THAT 50 AND MINUTES ARE LESS THAN 30 MINS

A

SELECT * FROM exercise_logs WHERE calories > 50 AND minutes < 30;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

/ OR / OPERATOR

A

SELECT * FROM exercise_logs WHERE calories > 50 OR heart_rate > 100;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

USING WHERE AND OR OPERATOR TOGETHER

A

SELECT title FROM songs ;
SELECT title FROM songs WHERE mood = “epic”
OR releaseD > 1990 ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Several Where and AND

A

SELECT title FROM songs ;
SELECT title FROM songs WHERE mood = “epic”
OR released > 1990 ;
SELECT title FROM songs WHERE mood = “epic”
AND released > 1990 AND duration <= 240;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

SHOW JUST TYPING LOGS

A

SELECT * FROM exercise_logs WHERE type = “biking”;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

FIND TYPE OF ACTIVITIES THAT ARE OUTSIDE ACTIVITIES USING WHERE AND OR OPERATORS

Second example show IN operator

A

SELECT * FROM exercise_logs WHERE type = “biking” OR type = “hiking” OR type = “tree climbing” OR type = “rowing”;

second example

SELECT * FROM exercise_logs WHERE type IN (“biking”, “hiking”, “tree climbing”, “rowing”);

or not

SELECT * FROM exercise_logs WHERE type IN (“biking”, “hiking”, “tree climbing”, “rowing”);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

creation of new table

A

INSERT INTO drs_favorites(type, reason) VALUES (“biking”, “Improves endurance and flexibility.”);
INSERT INTO drs_favorites(type, reason) VALUES (“hiking”, “Increases cardiovascular health.”);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

FIND DOCTOR RECOMMENDED ACTIVITY

A

SELECT type FROM drs_favorites;

SELECT * FROM exercise_logs WHERE type IN (
SELECT type FROM drs_favorites);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

SELECT type FROM drs_favorites WHERE reason = “Increases cardiovascular health.”);

A

SELECT type FROM drs_favorites WHERE reason = “Increases cardiovascular health.”);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

like use

A

SELECT type FROM drs_favorites WHERE reason = “Increases cardiovascular health.”);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

find sum of calories by type of activities and create new column in table

A

SELECT type, SUM(calories) AS total_calories FROM exercise_logs GROUP BY type;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

HAVING IS USED FOR TOTAL GROUP AFTER GROUP BY AND AND INDIVIDUAL CALCULATIONS

A

SELECT * FROM exercise_logs;

SELECT type, SUM(calories) AS total_calories FROM exercise_logs GROUP BY type;

SELECT type, SUM(calories) AS total_calories FROM exercise_logs
GROUP BY type
HAVING total_calories > 150

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Another having problem

A

SELECT * FROM exercise_logs;

SELECT type, AVG(calories) AS avg_calories FROM exercise_logs
GROUP BY type
HAVING total_calories > 50

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

SELECT type FROM exercise_logs GROUP BY type HAVING COUNT(*)

A

SELECT type FROM exercise_logs GROUP BY type HAVING COUNT(*)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

PERFECT HAVING EXAMPLE remember no comma

A

SELECT author, sum(words) as total_words from books

GROUP BY author

HAVING total_words>1000000;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

We’ve created a database of a few popular authors and their books, with word counts for each book. In this first step, select all the authors who have written more than 1 million words, using GROUP BY and HAVING. Your results table should include the ‘author’ and their total word count as a ‘total_words’ column.

A

SELECT author, sum(words) as total_words from books

GROUP BY author

HAVING total_words>1000000;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Now select all the authors that write more than an average of 150,000 words per book. Your results table should include the ‘author’ and average words as an ‘avg_words’ column.

A

SELECT author, avg(words) as avg_words

FROM books

GROUP BY author

HAVING avg_words>150000;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

What does an organization ask you to do?

A

Purchase habits, how many customer use A service, How many use A and B, what is the age age range customer selects what service, how often users login, what is the average lengh of time user login any time

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

Heart rate count example

A

SELECT COUNT(*) FROM exercise_logs WHERE heart_rate > 220 - 30;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

target rate heart btwn 50% n 80%

A

/* 50-90% of max*/

SELECT COUNT(*) FROM exercise_logs WHERE
heart_rate >= ROUND(0.50 * (220-30))
AND heart_rate <= ROUND(0.90 * (220-30));

46
Q

Use of CASE PART 1 & PART 2

A

PART 1

/* CASE */
SELECT type, heart_rate FROM exercise_logs;

PART 2

/* CASE */

SELECT type, heart_rate,
CASE
WHEN heart_rate > 220-30 THEN “above max”
WHEN heart_rate > ROUND(0.90 * (220-30)) THEN “above target”
WHEN heart_rate > ROUND(0.50 * (220-30)) THEN “within target”
ELSE “below target”
END as “hr_zone”
FROM exercise_logs;

47
Q

CASE PART 3

A

/* CASE */
SELECT type, heart_rate,
CASE
WHEN heart_rate > 220-30 THEN “above max”
WHEN heart_rate > ROUND(0.90 * (220-30)) THEN “above target”
WHEN heart_rate > ROUND(0.50 * (220-30)) THEN “within target”
ELSE “below target”
END as “hr_zone”
FROM exercise_logs;

48
Q

In this first step, select all of the rows, and display the name, number_grade, and percent_completed, which you can compute by multiplying and rounding the fraction_completed column.

A

SELECT name, number_grade, ROUND (fraction_completed*100) AS percent_completed FROM student_grades;

49
Q

The goal is a table that shows how many students have earned which letter_grade. You can output the letter_grade by using CASE with the number_grade column, outputting ‘A’ for grades > 90, ‘B’ for grades > 80, ‘C’ for grades > 70, and ‘F’ otherwise. Then you can use COUNT with GROUP BY to show the number of students with each of those grades.

A

SELECT COUNT (*),

CASE

WHEN number_grade > 90 THEN “A”

WHEN number_grade > 80 THEN “B”

WHEN number_grade > 70 THEN “C”

ELSE “F”

END as “letter_grade” FROM student_grades

GROUP BY “letter_grade”;

50
Q

Identity and create column for particular time decade

A

SELECT COUNT (*),

CASE

WHEN Year >= 2000 THEN “2000’s Movies”

WHEN Year <= 1999 THEN “90’s Movies”

ELSE “Unknown”

END as “Movie_Time_Decade” FROM topmovies

GROUP BY “Movie_Time_Decade”;

51
Q

AVERAGE EXER burns more than 50 cals

A

SELECT * FROM exercise_logs;

SELECT type, AVG(calories) AS avg_calories FROM exercise_logs
GROUP BY type
HAVING total_calories > 50

52
Q

Return the customer IDs of customers who have spent at least $110 with the staff member who has an ID of 2.

A

SELECT customer_id,SUM(amount)

FROM payment

WHERE staff_id = 2

GROUP BY customer_id

HAVING SUM(amount) > 110;

53
Q

How many films begin with the letter J?

A

SELECT COUNT(*) FROM film

WHERE title LIKE ‘J%’;

54
Q

What customer has the highest customer ID number whose name starts with an ‘E’ and has an address ID lower than 500?

A

SELECT first_name,last_name FROM customer

WHERE first_name LIKE ‘E%’

AND address_id <500

ORDER BY customer_id DESC

LIMIT 1;

55
Q

AS CLAUSE

A

SELECT column_name AS new_name

FROM TABLE

56
Q

SELECT amount AS rental_price FROM payment

A

SELECT amount AS rental_price FROM paymen

57
Q

SELECT SUM(amount) AS net_revenue FROM payment;

A

SELECT SUM(amount) AS net_revenue FROM payment;

58
Q

How much each customer has spent

A

SELECT customer_id, Sum(amount)
From payment
GROUP BY customer_id;

or

SELECT customer_id, SUM(amount) AS total_spend
FROM payment
GROUP BY customer_id;

59
Q

SELECT customer_id, SUM(amount) AS total_spend
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100;

A

SELECT customer_id, SUM(amount) AS total_spend
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100;

60
Q

SELECT INNER JOIN CODE

A

SELECT * FROM table_a
INNER JOIN table_b
ON table_A.colum_match=table_B.columnmatch

SELECT*FROM Registrations
INNER JOIN Logins
ON Registrations.name=Logins.name

SELECT reg_id, Logins.name, log_id
SELECT*FROM Registrations
INNER JOIN Logins
ON Registrations.name=Logins.name

61
Q

Innner join coding sample

A

SELECT*FROM customer
INNER JOIN payment
On customer.customer_id=payment.customer_id

SELECT*FROM customer
INNER JOIN payment
On customer.customer_id=payment.customer_id

SELECT payment_id, payment.customer_id, first_name FROM customer
INNER JOIN payment
On customer.customer_id=payment.customer_id

62
Q

OUTER JOIN CODE

A

SELECT * FROM TABLE_B
FULL OUTER JOIN TABLEA
ON TABL_B.COL_MATCH-TABLEA_MATCHTABLE

EXAMPLE

SELECT*FROM Registration
FULL OUT JOIN Logins
ON Registration.name=Logins.name

EXAMPLE

SELECT*FROM Registration
FULL OUT JOIN Logins
ON Registration.name=Logins.name
WHERE Registration.id IS null OR
Logins.id IS null
63
Q

FULL OUTER JOIN CODE / NULL OF BOTH TABLE UNIQUENESS

A 
SELECT*FROM payment
FULL OUTER JOIN customer
ON customer.customer_id = payment.customer_id
WHERE payment.customer_id IS null OR
customer.customer_id IS null
64
Q

LEFT OUTER JOIN CODE

A

SELECT*FROM payment
left OUTER JOIN customer
ON customer.customer_id = payment.customer_id

65
Q

LEFT JOIN ONLY UNIQUE TO TABLE B AND NOT FOUND IN TABLE A (payment) and only found in table b

A

SELECT*FROM payment
left OUTER JOIN customer
ON customer.customer_id = payment.customer_id
WHERE customer.customer_id IS null

66
Q

SELECT film.film_id, title, inventory_id
FROM film
LEFT OUTER JOIN inventory
ON inventory.film_id = film.film_id

A

SELECT film.film_id, title, inventory_id
FROM film
LEFT OUTER JOIN inventory
ON inventory.film_id = film.film_id

67
Q 
SELECT film.film_id, title, inventory_id,store_id 
FROM film
LEFT OUTER JOIN inventory 
ON inventory.film_id = film.film_id
WHERE inventory.film_id IS null
A 
SELECT film.film_id, title, inventory_id,store_id 
FROM film
LEFT OUTER JOIN inventory 
ON inventory.film_id = film.film_id
WHERE inventory.film_id IS null
68
Q

SELECT film.film_id, title, inventory_id
FROM film
right OUTER JOIN inventory
ON inventory.film_id = film.film_id

A

SELECT film.film_id, title, inventory_id
FROM film
right OUTER JOIN inventory
ON inventory.film_id = film.film_id

69
Q

UNION

A

SELECTFROM Sales2021_Q1
UNION
SELECTFROM Sales2021_Q2

OR ADD ORDER BY
SELECT*FROM Sales2021_Q1
UNION
SELECT*FROM Sales2021_Q2
Order by name
70
Q

What are the customer email of customer who live in california?

A

SELECT district, email FROM address
INNER JOIN customer
ON address.address_id=customer.address_id
WHERE district = “california”

71
Q

JOIN CODE /* INNER JOIN */

A

SELECT persons.name, hobbies.name FROM persons
JOIN hobbies
ON persons.id = hobbies.person_id
WHERE persons.name = “Bobby McBobbyFace”

72
Q

SELECT students.first_name, students.last_name, student_projects.title
FROM students
JOIN student_projects
ON students.id = student_projects.student_id;

A

SELECT students.first_name, students.last_name, student_projects.title
FROM students
JOIN student_projects
ON students.id = student_projects.student_id;

73
Q

SELECT customers.name, customers.email, orders.item, orders.price
FROM customers
LEFT OUTER JOIN orders
ON customers.id=orders.customer_id;

A 
SELECT customers.name, customers.email, orders.item, orders.price AS spent FROM customers
LEFT OUTER JOIN orders
ON customers.id = orders.customer_id
GROUP BY customers.name
ORDER BY spent desc;
74
Q

SELECT customers.name,customers.email,sum(orders.price) as spent

FROM customers

LEFT OUTER JOIN orders

ON customers.id=orders.customer_id

GROUP BY customers.name

ORDER BY spent desc;

A

SELECT c.name,c.email,sum(o.price) as spent

FROM customers c

LEFT OUTER JOIN orders o

ON c.id=o.customer_id

GROUP BY c.name

ORDER BY spent desc;

75
Q

SELF JOIN

SELECT students.first_name, students.last_name, buddies.email as buddy_email
FROM students
JOIN students buddies
ON students.buddy_id = buddies.id;

A

SELF JOIN

SELECT students.first_name, students.last_name, buddies.email as buddy_email
FROM students
JOIN students buddies
ON students.buddy_id = buddies.id;

76
Q 
SELECT a.fullname, b.fullname 
FROM friends
JOIN persons a
ON a.id = friends.person1_id
JOIN persons b
ON b.id = friends.person2_id;
A 
SELECT a.fullname, b.fullname 
FROM friends
JOIN persons a
ON a.id = friends.person1_id
JOIN persons b
ON b.id = friends.person2_id;
77
Q

USE NEXT TIME

A

SELECT people.show_id, people.director, titles.title, titles.type
FROM “CharlotteChaze/BreakIntoTech”.”netflix_people” people
LEFT JOIN “CharlotteChaze/BreakIntoTech”.”netflix_titles_info” titles
ON people.show_id = titles.show_id ;

78
Q 
SELECT TIMEOFDAY ( )
SELECT NOW ( )
SELECT CURRENT_TIME
A 
SELECT TIMEOFDAY ( )
SELECT NOW ( )
SELECT CURRENT_TIME
79
Q

EXTRACT ( )
AGE ()
TO_CHAR( )

A

EXTRACT ( )
AGE ()
TO_CHAR( )

80
Q

EXTRACT (YEAR FROM date_col)

A

EXTRACT (YEAR FROM date_col)

81
Q 
SELECT EXTRACT(YEAR FROM payment_date)
FROM payment
SELECT EXTRACT(QUARTER FROM payment_date)
FROM payment
A 
SELECT EXTRACT(YEAR FROM payment_date)
FROM payment
SELECT EXTRACT(QUARTER FROM payment_date)
FROM payment
82
Q 
SELECT TO_CHAR(payment_date, 'MONTH-YYYY')
FROM payment
A 
SELECT TO_CHAR(payment_date, 'MONTH-YYYY')
FROM payment
83
Q 
SELECT TO_CHAR(payment_date, 'MM-DD-YYYY')
FROM payment
A 
SELECT TO_CHAR(payment_date, 'MM-DD-YYYY')
FROM payment
84
Q 
SELECT DISTINCT (TO_CHAR(payment_date, 'MONTH')) 
FROM payment
A 
SELECT DISTINCT (TO_CHAR(payment_date, 'MONTH')) 
FROM payment
85
Q

UPDATE diary_logs SET content = “I had a horrible fight with OhNoesGuy” WHERE user_id=1 AND date = “2015-04-01”;

A

UPDATE diary_logs SET content = “I had a horrible fight with OhNoesGuy” WHERE user_id=1 AND date = “2015-04-01”;

86
Q

UPDATE diary_logs SET content = “I had a horrible fight with OhNoesGuy” WHERE id = 1;

A

UPDATE diary_logs SET content = “I had a horrible fight with OhNoesGuy” WHERE id = 1;

87
Q

DELETE FROM diary_logs WHERE id = 1;

SELECT * FROM diary_logs;

A

DELETE FROM diary_logs WHERE id = 1;

SELECT * FROM diary_logs;

88
Q

INSERT INTO diary_logs (user_id, date, content) VALUES (1, “2015-04-01”,
“I had a horrible fight with OhNoesGuy and I buried my woes in 3 pounds of dark chocolate.”);

A

INSERT INTO diary_logs (user_id, date, content) VALUES (1, “2015-04-01”,
“I had a horrible fight with OhNoesGuy and I buried my woes in 3 pounds of dark chocolate.”);

89
Q

ALTER TABLE diary_logs ADD emotion TEXT;

A

ALTER TABLE diary_logs ADD emotion TEXT;

90
Q

ALTER TABLE diary_logs ADD emotion TEXT;

INSERT INTO diary_logs (user_id, date, content) VALUES (1, “2015-04-02”,
“OhNoesGuy and I made up and now we’re bes, emotiont friends forever and w3 celebrated with a tub of ice cream.”);
We went to Disneyland!, “happy”

A

ALTER TABLE diary_logs ADD emotion TEXT;

INSERT INTO diary_logs (user_id, date, content) VALUES (1, “2015-04-02”,
“OhNoesGuy and I made up and now we’re bes, emotiont friends forever and w3 celebrated with a tub of ice cream.”);
We went to Disneyland!, “happy”

91
Q

delete all table

A

ALTER TABLE diary_logs ADD emotion TEXT;

INSERT INTO diary_logs (user_id, date, content) VALUES (1, “2015-04-02”,
“OhNoesGuy and I made up and now we’re bes, emotiont friends forever and w3 celebrated with a tub of ice cream.”);
We went to Disneyland!, “happy”

92
Q

SELECT * FROM clothes;

INSERT INTO clothes (type, design, price) VALUES (“pants”, “rainbow”, 40.00);

A

SELECT * FROM clothes;

INSERT INTO clothes (type, design, price) VALUES (“dress”, “pink polka dots”, 10.00);

93
Q

ALTER TABLE clothes ADD price INTEGER;

SELECT * FROM clothes;

UPDATE clothes SET price = 10 WHERE id = 1;
UPDATE clothes SET price = 20 WHERE id = 2;
UPDATE clothes SET price = 30 WHERE id = 3;

SELECT * FROM clothes ;

A

ALTER TABLE clothes ADD price INTEGER;

SELECT * FROM clothes;

UPDATE clothes SET price = 10 WHERE id = 1;
UPDATE clothes SET price = 20 WHERE id = 2;
UPDATE clothes SET price = 30 WHERE id = 3;

SELECT * FROM clothes ;

94
Q

SELECT * FROM clothes ;

INSERT INTO clothes (type, design, price) VALUES (“pants”, “rainbow”, 40.00);
SELECT*FROM clothes;

A

SELECT * FROM clothes ;

INSERT INTO clothes (type, design, price) VALUES (“pants”, “rainbow”, 40.00);
SELECT*FROM clothes;

95
Q

During which months did payment occurred?

A 
SELECT DISTINCT(TO_CHAR(payment_date, 'Month')
FROM payment
96
Q

HOW MANY PAYMENTS OCCURED ON A MONDAY?

dow = monday 0=sunday monday=1 etc, SQL standard

A 
Select COUNT (*)
FROM payment
Where EXTRACT (dow FROM payment_date) = 1
97
Q

WHAT IS THE RENTAL RATE OF THE REPLACEMENT COST.

A

SELECT rental_rate/replacement_cost FROM film OR

SELECT ROUND(rental_rate/replacement_cost*100) FROM film
OR
SELECT ROUND(rental_rate/replacement_cost*100, 2) FROM film
98
Q

HOW TO FIND LENGTH OF STRING - lastname

A

SELECT LENGTH(last_name) FROM customer

99
Q

contatenation - togehter

A

SELECT first_name || last_name FROM customer

Add Space string

SELECT first_name || ‘ ‘ || last_name FROM customer

100
Q

UPPER CASE STRINGS

A

SELECT UPPER(first_name) || ‘ ‘ || UPPER(last_name) FROM customer

101
Q

CREATE CUSTOMER EMAILS FOR EMPLYEES USING FIRST LETTER OF NAME, FULL LAST NAME AND THEN @WHATEVER

A

SELECT LEFT(first_name, 1) || ‘ ‘ || last_name || ‘@gmail.com’ FROM customer

CORRECTION

SELECT LEFT(first_name, 1) || last_name || ‘@gmail.com’ FROM customer

102
Q

CREATE EMAIL FOR EMPLOY

A

SELECT LOWER (LEFT(first_name, 1)) || LOWER(last_name) || ‘@gmail.com’ FROM customer

SELECT LOWER (LEFT(first_name, 1)) || LOWER(last_name) || ‘@gmail.com’ AS custom_email FROM customer

103
Q

FIND STUDENT WHO SCORE HIGHER THAN THE AVERAGE, THUS THIS INVOLVES SUBQUERIES

A

SELECT student, grade
FROM test_scores
WHERE grade > (SELECT AVG(grade) FROM test_scores)

104
Q

FIND STUDENTS IN THE HONOR ROLL

A

SELECT student, grade
FROM test_scores
WHERE student In (SELECT student FROM honor_roll_table)

105
Q

SELECT FILMS WITH RENTAL RATES HIGHER THAN THE AVERAGE RATE

A

SELECT title, rental_rate
FROM film
WHERE rental_rate > (SELECT AVG(rental_rate) FROM film)

106
Q

SELECT FILM TITLE THAT HAVE BEEN RETURNED BETWEEN A CERTAIN SET OF DATES

A

SELECT * FROM rental

WHERE return_date BETWEEN ‘2005-05-29’AND ‘2005-05-30’

107
Q

SELECT inventory.film_id
FROM rental
INNER JOIN inventory
ON inventory.inventory_id=rental.inventory_id
WHERE return_date BETWEEN ‘2005-05-29’AND ‘2005-05-30’

A

SELECT inventory.film_id
FROM rental
INNER JOIN inventory
ON inventory.inventory_id=rental.inventory_id
WHERE return_date BETWEEN ‘2005-05-29’AND ‘2005-05-30’

108
Q 
SELECT film_id, title
FROM FILM
WHERE film_id IN
(SELECT inventory.film_id 
FROM rental
INNER JOIN inventory
ON inventory.inventory_id=rental.inventory_id
WHERE return_date BETWEEN '2005-05-29'AND '2005-05-30')
A 
SELECT film_id, title
FROM FILM
WHERE film_id IN
(SELECT inventory.film_id 
FROM rental
INNER JOIN inventory
ON inventory.inventory_id=rental.inventory_id
WHERE return_date BETWEEN '2005-05-29'AND '2005-05-30')
109
Q

SELECT first_name, last_name
FROM customer AS C
WHERE EXISTS(SELECT * FROM payment aS P WHERE p.customer_id=c.customer_id AND amount > 11)

A

SELECT first_name, last_name
FROM customer AS C
WHERE EXISTS(SELECT * FROM payment aS P WHERE p.customer_id=c.customer_id AND amount > 11)

110
Q

SELECT emp.name, report.name
From employees AS emp
Join employees AS report
ON emp.employ_id=report.report_id

A

SELECT emp.name, report.name AS rep
From employees AS emp
Join employees AS report
ON emp.employ_id=report.report_id

SQL (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions