1.3.2 - databases Flashcards
4
entity
A category of things/people of interest to a business that data is to be recorded about.
flat file database
Consists of a single file.
relational database
A separate table is created for each entity identified in the system.
foreign key
A field that links two tables, links to a primary key in a second table.
composite key
A primary key that consists on more than one attribute.
Basic structure of an SQL query
SELECT
FROM
WHERE
SQL query to select all fields from a table
SELECT *
SQL query to select all records starting with ‘A’
WHERE…LIKE “A%”
SQL query to insert new records
INSERT INTO <table name> (<field1>, <field2>)
VALUES (<value1>, <value 2>)</value1></field2></field1>
SQL query to delete an entity
DELETE FROM <table name>
WHERE <field> = <condition></condition></field>
SQL query to update an record
UPDATE <table name>
SET <field> = <value>
WHERE <field> = <condition></condition></field></value></field>
SQL query to delete a table
DROP TABLE <table name>
SQL join
SELECT Song. SongTitle,, Artist .ArtistName, Song. MusicType
FROM Song
JOIN Artist
ON Song.ArtistID = Artist.ArtistID
WHERE Song.MusicType = ’’Art Pop”
SQL query to add a field
ALTER TABLE Employee
ADD Department VARCHAR(IO)
SQL query to change the data type of a column
ALTER TABLE Employee
MODIFY COLUMN EmpName VARCHAR(30)NOT NULL