-- create a table
CREATE TABLE Student(
  id int NOT NULL UNIQUE,
  name varchar(30),
  major varchar(30) DEFAULT 'undecided',
  year int DEFAULT 1,
  NumCredits int
);

INSERT INTO Student (id, name, major, year) VALUES
(1, 'mahi','computer science',4),
(2, 'emma', 'IIT',2),
(3, 'carolina','computer information systems',3),
(4, 'lea', 'business','1');

--display all doing this below
SELECT * FROM Student;

--displaying one column
SELECT name, year FROM Student;

--ascending // descending order
SELECT name, year FROM Student ORDER BY year ASC;
SELECT name, year FROM Student ORDER BY year desc;

--aliases
SELECT name AS student_name FROM Student;

--Filter
SELECT name FROM Student WHERE year = 4;
SELECT name FROM Student WHERE year < 4;
SELECT name FROM Student WHERE year != 1;

--deleting from a TABLE... uncomment the statement below to see.
--DELETE FROM Student WHERE year = 4;
SELECT * FROM Student;

--text operators (finding text)
-- % means start or ends with. If its before (%s), it's ends with. If its after (r%), its starts with.
SELECT name FROM Student WHERE name LIKE 'c%';
SELECT name FROM Student WHERE major LIKE '%usi%'; -- this means 'contains these characters'

-- insert more
INSERT INTO Student(id, name, major, year, NumCredits) VALUES
(5, 'max', 'biology', 2, 50);

SELECT * FROM Student;

-- more operators
SELECT name FROM Student WHERE year BETWEEN 1 AND 3;

SELECT name FROM Student WHERE NumCredits IS NOT NULL;

SELECT name FROM Student WHERE NumCredits IS NULL;

SELECT name FROM Student WHERE id IN (2,3,4);

SELECT DISTINCT(major) FROM Student;

SELECT COUNT(id) AS Num_Students, year
FROM Student
GROUP BY year
ORDER BY year ASC;

SELECT avg(NumCredits) FROM Student;
SELECT COUNT(*) FROM Student; -- asterisk is a wild card meaning 'all'
SELECT max(year) FROM Student;
SELECT min(year) FROM Student;
SELECT sum(year) FROM Student;

--Updating the TABLE
UPDATE Student 
SET year = 5
WHERE name = 'mahi';

SELECT * FROM Student;

--Joins!
CREATE TABLE WicMembers (
  StudentName varchar(30) NOT NULL,
  yearsofMembership int DEFAULT 1,
  NumMeetings int DEFAULT 0);

INSERT INTO WicMembers (StudentName, yearsofMembership, NumMeetings)
VALUES ('mahi', 4, 30),
('emma', 3, 20),
('laura', 3, 10);

-- inner joins return all the rows from both tables. 
--if there are no matching rows in second table, they aren't shown
SELECT name, major, year, WicMembers.yearsofMembership, WicMembers.NumMeetings
FROM Student INNER JOIN WicMembers ON name = WicMembers.StudentName;

-- full join (shows nulls, displays all)
SELECT name, major, year, WicMembers.yearsofMembership, WicMembers.NumMeetings
FROM Student FULL JOIN WicMembers ON name = WicMembers.StudentName;

--LEFT JOIN (returning everything from the left with a match on the right!)
-- in this sense, student is the left and wic is the right table!
SELECT name, major, year, WicMembers.yearsofMembership, WicMembers.NumMeetings
FROM Student LEFT JOIN WicMembers ON name = WicMembers.StudentName;

--RIGHT JOIN 
SELECT name, major, year, WicMembers.yearsofMembership, WicMembers.NumMeetings
FROM Student RIGHT JOIN WicMembers ON name = WicMembers.StudentName;

--CROSS JOIN (pulls every possible combination from both tables!)
SELECT name, WicMembers.yearsofMembership
FROM Student, WicMembers;

--