SELECT

DISTINCT - SELECT COUNT(DISTINCT customer_id) FROM payment

COUNT - SELECT COUNT (amount) from PAYMENT

AND

WHERE

OR

ORDER BY – DESC/ASC - SELECT customer_id, sum(amount) from PAYMENT

GROUP BY customer_id

ORDER BY SUM(amount) DESC

Limit

Between

In - he IN operator allows you to specify multiple values in a WHERE clause.

SELECT * from cd.facilities

WHERE facid IN ('1','5')

And

Begin with a - SELECT COUNT(*) from film

where title LIKE 'J%'

End with a – SELECT COUNT(*) from film

where title LIKE %'J'

Should Include tennis word anywhere - '%Tennis%'

Name like ‘a%’ or use ‘ilike’

‘%’ for long sentence characters and ‘_’ allows us to replace just a single character

Section – 3

AVG – Round off - select ROUND (AVG (replacement_cost),2) FROM film;

Count - SELECT COUNT (*) FROM film;

Max - SELECT MAX (replacement_cost) FROM film;

Min - SELECT MIN (replacement_cost) FROM film;

Sum - SELECT SUM (replacement_cost) FROM film;

Group by - SELECT customer_id from PAYMENT

Date - SELECT DATE(payment_date) from PAYMENT

HAVING - SELECT store_id,COUNT(*) FROM customer

GROUP BY store_id

HAVING COUNT(*) > 300

Section – 5

AS - Select SUM(AMOUNT) as net_revenue FROM payment;

INNER JOIN – (intersection)

SELECT * from payment

INNER JOIN customer

ON payment.customer_id = Customer.customer_id

OUTER JOIN - SELECT * FROM customer

FULL OUTER JOIN payment

ON customer.customer_id = payment.customer_id

LEFT OUTER JOIN – ( ONLY A )

SELECT film.film_id,title,inventory_id

from film

LEFT JOIN inventory on

inventory.film_id = film.film_id

RIGHT JOIN – It will take right side of the query

UNION –

Section - 7

SHOW - SHOW TIMEZONE

Current time stand - SELECT NOW ()

SELECT TIMEOFDAY ()

SELECT CURRENT_DATE

SELECT CURRENT_TIME

EXTRACT - SELECT EXTRACT (YEAR FROM payment_date) FROM payment

TO CHARACTER - SELECT To_CHAR (payment_date, 'dd/MM/YY' ) FROM payment

https://www.postgresql.org/docs/12/functions-formatting.html

Mathematical Operators - We can use 8, +, - basic signs to calculate

For percentage - SELECT ROUND(rental_rate/replacement_cost,2)*100 FROM film

Length of letters - SELECT LENGTH(first_name) FROM customer

|| - Join 2 or more columns - SELECT first_name || last_name || '@gmail.com' FROM customer

Sub query - SELECT title, rental_rate from film

WHERE rental_rate >

(SELECT AVG(rental_rate) from film)

Self Join - SELECT f1.title, f2.title, f1.length from film as f1

INNER JOIN film as f2 on

f1.film_id != f2.film_id

AND f1.length = f2.length

Unit – 8

Primary Key – Having unique value in every row and coloumns ( Golden Key)

Foreign Key – Conatraints

Creating NEW TABLE - CREATE TABLE account(

user_id SERIAL PRIMARY KEY,

username VARCHAR(50) UNIQUE NOT NULL,

Password VARCHAR(50) NOT NULL,

email VARCHAR(250)UNIQUE NOT NULL,

created_on TIMESTAMP NOT NULL,

last_login TIMESTAMP

)
Create data on Tables -

INSERT INTO account(username, password, email, created_on)

VALUES

('Jose', 'password','[email protected]', CURRENT_TIMESTAMP)

Update data –

UPDATE account

SET last_login = CURRENT_TIMESTAMP

Deleting data

DELETE FROM job

WHERE job_name = 'cowboy'

RETURNING job_id, job_name

Altering data of tables –

1. ALTER TABLE information

RENAME TO new_info

2. ALTER TABLE new_info

Dropping columns from table –

ALTER TABLE new_info

DROP COLUMN people

Sol - SELECT staff_id, COUNT (amount) from PAYMENT

Ques. We're running a promotion to reward our top five customers with coupons. What are the
customer IDs of the top five customers based off total expenditure or total spend?

Sol. SELECT customer_id, sum(amount) from PAYMENT

GROUP BY customer_id

order by sum(amount) DESC

LIMIT 5

Ques We are launching a platinum service for our most loyal customers. We will assign platinum
status to customers that have had 40 or more transaction payments. What customer IDs are
eligible for platinum status?

Sol. SELECT customer_id, count(amount) from PAYMENT

GROUP BY customer_id

HAVING count(amount) > 39

1. Return the customer IDs of customers who have spent at least $110 with the
staff member who has an ID of 2.
Sol. SELECT customer_id, sum(amount) from PAYMENT

WHERE staff_id = 2
GROUP BY customer_id

HAVING sum(amount) >= 110

3. What customer has the highest customer ID number whose name starts with an 'E' and has an
address ID lower than 500?

SELECT * from Customer

WHERE first_name like 'E%'

AND address_id < 500

ORDER BY customer_id DESC

LIMIT 1

Ques So let's imagine that a California sales tax law has changed, and we need to alert our
customers to this through email. So the question is, what are the emails of the customers who
live in California?

Ans. SELECT district, email from address

INNER JOIN customer

ON customer.address_id = address.address_id

WHERE DISTRICT = 'California'

Ques. A customer walks into the store and they're a huge fan of this actor, Nick Wahlberg, and
they want to know which movies has Nick Wahlberg been in. So your task is to basically get a
list of all the movies Nick Wahlberg has been in.So let's take a look at the expected results. Here
are the expected results. There should be 25 movies.

SELECT title, first_name, last_name from actor

INNER JOIN film_actor

ON actor.actor_id = film_actor.actor_id

INNER JOIN film

ON film_actor. film_id = film.film_id

last_name = 'Wahlberg'

QUES. Find out the month in which the payments occurred ?

Sol. SELECT DISTINCT (TO_CHAR (payment_date,'MON')) FROM payment

Q. Number of payments occurred on Monday ?

SELECT COUNT (*) FROM payment

WHERE EXTRACT (dow FROM payment_date) = 1

Assesment 2 -
https://docs.google.com/document/d/1wiuYbTQslmfolQWgeVPB356csjK6yqOUBhgC7fM44o8/edit

https://docs.google.com/document/d/
1swGZ0RG3KKqWqzmsI_qrMgjJ3lt39mtAJqRSMZy6Z-8/edit?usp=sharing

4, 11

SELECT * from cd.bookings

WHERE starttime BETWEEN '2012-09-01' AND '2012-010-01'