YEAR 2 CO1 WEEK 3 SQL Flashcards
What Is SQL?
Stands for Structured Query Language. A language to create a database with.
What is the command to set up a database?
Then explain what it does.
CREATE DATABASE test
Will cause the RDBMS to create supporting structures for a database called ‘test’. Empty at this point.
What is the command to create tables within the database?
Explain what the command does.
CREATE TABLE Clubs
Just the standard SQL command to create a table.
How do you create the fields in the tables of the database?
Explain what the command does.
CREATE TABLE Clubs (ClubID INT, ClubName VARCHAR(50) NOT NULL)
The parameters within the brackets define, as a minimum, each field name and their data type.
As seen with the second field of ClubName it is of type varChar with the length of 50 characters.
The added NOT NULL part insists on the field value not being empty.
How do you declare primary keys in the table?
Explain the command.
CREATE TABLE Clubs (…. , ….. , PRIMARY KEY (ClubID))
Tells the system that the primary key is the field ClubID
How do you add a field to an existing table?
ALTER TABLE tablename ADD columnname datatype
How do you remove a field from an existing table?
ALTER TABLE tablename DROP COLUMN columnname
How do you insert data into a table?
Explain the commands.
Done using the SQL INSERT command.
INSERT INTO test.clubs
This identifies the database and table to be used.
INSERT INTO test.clubs (ClubID, ClubName)
The fields that are to be loaded with new data.
INSERT INTO test.clubs (ClubID, ClubName) VALUES (1, ‘The Pullman Club’ );
Here are the actual values to be loaded into the fields.
How to update data in a table?
Explain the commands.
UPDATE test.clubs SET ClubName = ‘The Truman Club’ WHERE clubs.ClubID =1;
The word SET identifies the fields to be updated.
The WHERE part is very important pinning down exactly which records are to be updated. Otherwise all records will be overwrited.
How to extract all records from a table?
Explain the commands.
The SELECT command Is used to extract records from tables.
SELECT * FROM clubs
Star means all fields.
How to select several records from a table?
Explain the commands.
SELECT * FROM clubs WHERE clubID>1
Here the WHERE clause selects any record whose primary key is greater than one.
How to select only one record from a table?
Explain the commands.
SELECT * FROM clubs WHERE ClubID=2
Returns a single record which of the primary key is equal to 2.
How to select only one record from a table?
Explain the command.
SELECT * FROM clubs WHERE ClubID=2
Returns a single record whose primary key is equal to 2.
How to select data from several records?
Explain the commands.
SELECT fieldname(s) FROM table1
JOIN table2
ON table1.fieldname = table2.fieldname
Here we are using primary and foreign keys to link tables.
The JOIN command is used to include more than one table in the search.
