SQL Flashcards
define relational database
database that organizes information into one or more tables
what does sql stand for
structured query language
Describe the flow of the relational database management system
the relational database stores data in tables, and these tables provide data for view by queries. these queries can change the table, format data from table into reports, or organise data sequentially into procedures
what are fields
a fixed number of columns, in each of which is a fixed data type
what is a primary key
a field, or collection of fields,
whose data uniquely identify a record. primary key usually shown in bold. the column that is unique to each record in the table.
what are records
an unlimited set of rows, each row containing data in each column of a prescribed type
what is a foreign key
a field that refers to a primary key in another table
what is a schema
a map of tables, fields, and
relationships in a relational database
how do you capture many to many relationships in sql?
In general, if you have many-to-many relations,
you will need an extra table — junction/join table. One primary key has multiple fields: no one field by itself
is unique in the table
are sql words case sensitive
NO
difference between query and view
query is temporary, view can be saved and opened again
if you delete info using a query in sql, does it change the result in the table permanently
yes
what is the main principle in database design
do not duplicate data. cos 1) error rate high 2) waste memory 3) time consuming, need to change everything when you have one change. Good database design separates related data into tables so that the data are not needlessly repeated
describe the four types of sql joins
inner join: matching records from both tables
left join: all records from left table, and matching records from right
right join: all records from right, and matching records from left table
full outer join: all records, when there is either a match in left or right table
