SQL-Intro.sql

-- Create a database
 -- CREATE TABLE users(
 -- 	id SERIAL PRIMARY KEY,
	-- username VARCHAR(20),
	-- email VARCHAR(30),
	-- password VARCHAR(12),
	-- age INTEGER,
	-- isAdmin BOOLEAN
 --  );
-- Create a database
 -- CREATE TABLE pets(
 -- 	id SERIAL PRIMARY KEY,
	-- name VARCHAR(20),
	-- age INTEGER,
	-- ownerId INTEGER REFERENCES users(id)
 --  );

  

 -- add users to the database
-- INSERT INTO users(username, email, password, age, isAdmin) 
-- VALUES('Malek', '[email protected]', '1234', 21, false);
-- INSERT INTO users(username, email, password, age, isAdmin) 
-- VALUES('Bruno', '[email protected]', '1234', 25, false);
-- INSERT INTO users(username, email, password, age, isAdmin) 
-- VALUES('Stephan', '[email protected]', '1234', 16, true);
--  INSERT INTO users(username, email, password, age, isAdmin) 
-- VALUES('Joshua', '[email protected]', '1234', 38, true);
-- add pets
-- INSERT INTO pets(name, age, ownerId ) 
-- VALUES('Buddy', 13, 4);
-- INSERT INTO pets(name, age  ) 
-- VALUES('Sad Dog 1', 5);
-- INSERT INTO pets(name, age ) 
-- VALUES('Sad Dog 2', 5);

-- add a new column to the table
-- ALTER TABLE users
-- ADD id SERIAL;

-- select certain user
-- SELECT * FROM users
-- WHERE username='Bruno';
-- SELECT * FROM users 
-- WHERE age >= 25 OR isAdmin=true;


-- count all the users
-- SELECT COUNT(*) FROM users;

-- add all users ages
-- SELECT SUM(age) as total_age FROM users;
-- who is the oldest user
-- SELECT MAX(age) as oldest, username FROM users

-- update a user
-- UPDATE users 
-- SET email='[email protected]';

-- delete a user
-- DELETE FROM users;

--Delete Database
-- DROP TABLE users;

-- SELECT * FROM users;

-- ****************JOINS*******************
-- SELECT * FROM pets
-- INNER JOIN users
-- ON pets.ownerId = users.id;

-- SELECT * FROM pets
-- LEFT JOIN users
-- ON pets.ownerId = users.id;

-- SELECT * FROM pets
-- RIGHT JOIN users
-- ON pets.ownerId = users.id;