PostgreSQL - The queries cheat-sheet

pgsql_queries.txt

select version();

---MOVIES TABLE---
select * from movies;

--- insert rows ---
INSERT INTO movies (movie_id, movie_name, genre)
VALUES (101, 'rocket', 'comedy'),
(102, 'intersteller','horror' );

--- insert rows ---
INSERT INTO movies (movie_id, movie_name, genre, rating)
VALUES (107, 'timepass', 'romance', 7);

--- update --
UPDATE movies
SET genre = 'sci-fi'
WHERE movie_id = 101;

--- detete ---
DELETE FROM movies
where movie_id = 101

--- select ---
SELECT * from movies
WHERE rating<10;

SELECT * from movies
WHERE rating BETWEEN 7 AND 10;

SELECT movie_name, genre from movies
WHERE rating = 10;

SELECT * from movies WHERE rating IN(7,8,10);



---EMPLOYEES TABLE---

SELECT * FROM employees;

CREATE TABLE employees (emp_id SERIAL NOT NULL PRIMARY KEY, emp_name VARCHAR(20) NOT NULL, email VARCHAR(30) NOT NULL , dept VARCHAR(10) NOT NULL, country VARCHAR(10) NOT NULL);

INSERT INTO employees (emp_name,email,dept,country,salary)
VALUES ('raj','raj@gmail.com','design', 'usa', 90);

INSERT INTO employees (emp_name,email,dept,country)
VALUES ('sahil','sahil@gmail.com','tech', 'india'),
('ishant','ishant@gmail.com','product', 'india'),
('zeel','zeel@gmail.com','tech', 'india'),
('Prachi ','prac@gmail.com','design', 'usa');


SELECT country from employees
WHERE salary >90;

SELECT DISTINCT country from employees;

SELECT * from employees
WHERE email is not null;

SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary LIMIT 3;
SELECT * FROM employees ORDER BY salary LIMIT 3 OFFSET 2;
SELECT * FROM employees ORDER BY salary FETCH FIRST 3 ROWS ONLY;
SELECT * FROM employees ORDER BY salary OFFSET 3 FETCH FIRST 3 ROWS ONLY;
 
ALTER TABLE employees
RENAME COLUMN address to country;

SELECT * FROM employees
WHERE country = 'usa' and salary = 100;


--- LIKE operator for matching----
SELECT emp_name, email FROM employees WHERE emp_name LIKE '%tha%';

---SUM, AVG, MIN and MAX---
SELECT AVG(salary) as avg_sal FROM employees;
 
 ---COUNT---
SELECT COUNT(DISTINCT dept) AS total_dep FROM employees;

---UPDATES---
 UPDATE employees
 SET dept = 'design'
 WHERE dept is null;
 
 ---GROUP BY CLAUS---
 SELECT country, AVG(salary) as avg_sal FROM employees GROUP BY country;
 
 SELECT dept, AVG(salary) as avg_sal from employees GROUP BY dept;

-- HAVING BY CLAUS---
SELECT country, AVG(salary) as avg_sal FROM employees GROUP BY country HAVING avg(salary)>96;

SELECT dept, AVG(salary) as avg_sal from employees GROUP BY dept HAVING AVG(salary)>98;

SELECT country, COUNT(emp_id) as emp_count FROM employees GROUP BY country HAVING count(emp_id)<30 ORDER BY count(emp_id); 

---CASE EXPRESSIONS---
SELECT dept, country, salary,
CASE
WHEN salary >50 and salary <80 
THEN 'Low salary'
WHEN salary >80 and salary<100
THEN 'Medium salary'
WHEN salary >= 100
THEN 'Andha paisa'
END AS salary_range
FROM employees;

---SUB-QUERIES/ NESTED QUERIES ---

SELECT emp_name, email, dept, country FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

--- SQL FUNCTIONS ---
SELECT ABS(-3);

SELECT GREATEST(12,433,15);
SELECT LEAST(12,433,15);
SELECT MOD(49,5);
SELECT POWER(5,3);
SELECT SQRT(16);

SELECT SIN(30);
SELECT CEIL(4.43);
SELECT FLOOR(4.43);


--- STRING FUNCTION'S---
SELECT CHAR_LENGTH('duke');
SELECT CONCAT('thew', 'duke', 'awesoe')
SELECT LEFT('awesomerer',2);
SELECT RIGHT('awesomerer',2);
SELECT REPEAT('name_', 3);
SELECT REVERSE('INDIA');

-- USER DEFINED FUNCTIONS---

CREATE OR REPLACE FUNCTION
Count_Emails();
RETURNS INTEGER as $total_emails$
DECLARE total_emails INTEGER;
BEGIN
SELECT COUNT(email) INTO total_emails FROM employees;
RETURN total_emails;
END;
$total_emails$ LANGUAGE plpgsql;

---calling function
SELECT Count_Emails();