SQL

Question 1

how do I create a database

Answer 1

sqlite3 title.db or CREATE DATABASE title;

Question 2

how do I create a table

Answer 2

CREATE TABLE title (
column1 data type constraints,
column2 data type constraints,
etc.);

Question 3

how do I delete a table

Answer 3

DROP TABLE title;

Question 4

how do I put data in a table

Answer 4

INSERT INTO title (column, column, etc) VALUES (value, value, etc);

Question 5

how do I view a table

Answer 5

SELECT * FROM title;

Question 6

how do I view certain columns of a table

Answer 6

SELECT column, column, etc FROM table;

Question 7

how do I filter out the rows in a selection

Answer 7

SELECT column, column etc FROM table WHERE constraint;

Question 8

How do I change data once in a table already

Answer 8

UPDATE table SET column = new value WHERE constraint;

Question 9

how do I delete rows

Answer 9

DELETE FROM table WHERE constraint;

Question 10

How do I add a column to a table

Answer 10

ALTER TABLE table ADD new column;

Question 11

How do I delete a column in a table

Answer 11

ALTER TABLE table DROP column;

Question 12

How do I rename a column in a table

Answer 12

ALTER TABLE table RENAME column TO new name;

Question 13

How do I clear all data from a table

Answer 13

TRUNCATE TABLE table;

Question 14

What are some common constraints when creating columns in a table

Answer 14

NOT NULL means it needs to be given a value, UNIQUE means it can not have the same value as another row, DEFAULT allows you to set a default value if left empty, AUTO_INCREMENT will increase the id each time a row is created

Question 15

How can we do more complicated constraints on columns

Answer 15

can use CONSTRAINT label of constraint CHECK (set of constraints with logical connectives), or something like CONSTRAINT label of constraint UNIQUE(column, column) to make sure no two rows have the same values in both those columns