sql coding

What does primary key do?

Primary key: One or more columns that can be used as an unique identifier for each row in a table. it makes sure that the table row can not be empty and that will be unique, no two row can have the same value for primary key. Primary key is important as it will work on the relationship between other table and work with other table's foreign key.

Foreign key:One or more columns that can be used together to identify single row in another table. as the primary key this foreign key is also not null and it connect to the primary key.

CREATE DATABASE record_company;

//we want to use the database that we already have created

USE record_company;

CREATE TABLE bands(

    id INT NOT NULL AUTO_INCREMENT; //id is a column and after that we define the data type, we used auto increment as we want it to increment whenever the user put and update the id

    name VARCHAR(255) NOT NULL; //VARCHAR is like string but in sql we don't use string, we define how many character there can be stored

    PRIMARY KEY (id); //we use id as primary key

);

//we create another table

CREATE TABLE albums(

    id INT NOT NULL AUTO_INCREMENT; //as id is primary key so we had to define as not null

    name VARCHAR(255) NOT NULL;

    release_year INT;

    band_id INT NOT NULL;

    PRIMARY KEY (id);

    FOREIGN KEY (band_id) REFERENCES bands(id)  //we use band_id as foreign key to connect with the bands primary key

);

 

another one:

CREATE DATABASE industry;
USE industry;
CREATE TABLE corporation(
corp_id SMALLINT,
name VARCHAR(30),
CONSTRAINT pk_corporation PRIMARY KEY (corp_id)
);

INSERT INTO corporation(corp_id,name)
VALUES (27, 'Acme Paper Corporation');

 

SELECT: one or more things

FROM: one or more places

WHERE: one or more conditions apply

 

Hypothetically, we have a table:

txn_id    txn_type_cd    txn_date                            amount

11           DBT                2008-01-05 00:00:00        100.00

 

SELECT t.txn_id, t.txn_type_cd,t.txn_date, t.amount

FROM individual i

    INNER JOIN account a ON i.cust_id = a.cust_id

    INNER JOIN product p ON p.product = a.product_cd

    INNER JOIN transaction t ON t.account_id = a.account_id

WHERE i.fname = 'George' AND i.lname = 'Blake'

    AND p.name = 'checking account'

 

 

 

SELECT cust_id, fname

FROM individual

WHERE lname = 'Smith'