basicCommandsSQL.sql

--TABLE CREATION OR MODIFICATION
-- **************************************************
--Creates a new table with three columns, student_id being the primary key
--INT is interger, VARCHAR is string and the 2 is up to 20 in length
    --NOT NULL means that this value cannot be null or undefined
    --UNIQUE means it has to be unique 
    --AUTO_INCREMENT means the student id will keep counting base on the last entry
CREATE TABLE student (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    major VARCHAR(20) DEFAULT('undecided')
);
--return the description of the table that has its feilds
DESCRIBE student;
--delete the table
DROP TABlE student;
--Add a column to the student table of gpa that is a decimal number 3 digits with 2 after the .
ALTER TABLE student ADD gpa DEC(3,2);
--Delete a column from the student table with the name gpa
ALTER TABLE student DROP COLUMN gpa;

--QUERIES ON A DB NAMED student
--*******************************************
--find all students from the student table with the *
SELECT * FROM student;
--add a new student to the student table (the three attributes need to be in the correct order)
INSERT INTO student VALUES(1, "Kate", "Sociology");
--add a new student that does not have all the values
--add a student that is missing one attribute(it will be null)
--the () after student say which attributes that you do have
INSERT INTO student(name, major) VALUES("Rocio", "Chemistry");
INSERT INTO student(name, major) VALUES("Jay", 'Biology');

--update all columns in the table to be something else
UPDATE student
SET major= 'Bio'
WHERE major = 'Biology';
-- update a column if more than one thing is true
UPDATE student
SET major = 'Bio Chem'
WHERE major = 'Biology' OR major = 'Chemistry';
--update more than one column for one student with the id of 1
UPDATE student
SET name='blah', major='idk'
WHERE student_id = 1;
--set column for every student in the list
UPDATE student
SET major = 'this is everyone';
--only select certain columns from the table (one thing)
--this will retuen a list of only the students names and nothing else
SELECT name from student;
--only select certain columns from the table (multiple things are separated by comma)
--you can specify the table with student.major but not needed
SELECT name, student.major from student;
--you can alphabetize by sorting ASCENDING
SELECT *
FROM student
ORDER BY name;
--you can alphabetize by sorting DECSENDING
SELECT *
FROM student
ORDER BY name DESC;
--limit the amount of results
SELECT *
FROM student
LIMIT 2;
--filtering a list
--There are all acceptable with WHERE > < >= <= = AND OR !=  
SELECT *
FROM student
WHERE major = 'Biology';
--choosing multiple names is the db
SELECT * 
FROM student
WHERE name in ('Josh', 'Jay');