Chap 9 - databases, SQL Flashcards
define database
-structured collection of data that can be modified
-can have many tables in it
3 uses of storing on a databases & eg
1) people info
-patients in hospital
-students in skl
2) things info
-books in library
-cars sold
3) events info
-hotel booking
race results
define flat files
a simple table with fields and records and it not related to other databases
the problem with flat files
when data is changed in a file, it is not reflected in another
-causes confusion & inconsistency
solution to flat file (advantages over them)
-a central database that is accessible on diff applications
-changes made by 1 app are reflected on another
-1 central database needs less memory than many databases
-access rights on who can access, edit & modify info
eg, laptop, phone
format of a database
-tabular structure
-field - table with 1 type of info
records - row of all data on a person/ thing
field field field
info info info
data types in tables compared to python
text - string
character - character
boolean - boolean
integer - integer
real - float
data/ time eg. 2/2/12
uses of data types
-all field need a data type to classify how to store, display and perform operation on it
define primary key & its use
-a field with never repeating data values
-to uniquely identify records
-usually char & no.
-automatically generated in order
eg, C1, C2
-choose data type with no repeating data
how do you get a foreign key
-when a primary key is added in a table with an already existing primary key
-the old primary key is a foreign key which can repeat
eg. primary key - no. orders, foreign key - customer id
what would happen without primary key
-insertion issues - rows are uniquely identified
-updating issues
what is SQL
Structed Query Language
note: about SQL
-case insensitive
-no need indentation
- , separates items
-end each command with ;
-anything that is text, except command words, in “ “
command words for SQL
-create database
-drop database
-create table
-insert into
-select ___ from
-where
-sum
-count
-order by
create database
create database database name;
create table
create table table name(
field name data type & (limit),
field name data type & (limit)
);
-comma separates fields
-add limit in ( ) depending on data type
-select database before making table
-( ) for everything before ;
how to write data types in SQL & limit
text - varchar (250) - max limit is 255 so it doesn’t go over 1 byte
integer - int - no limit
insert data in table
insert into table name (field name, field name, field name)
values
(info, info, info),
(info, info, info); -end of line so add ;
-add “ “ for varchar
-write values in order of fields
-if not enough field, remaining data is left
select data in table
1) selecting everything: output - table, data
select * from table name;
2) select 1 field: output - specific field, data
select field name from table name;
3) select many fields: add comma after fields
select field name , field name from table name;
select data based on condition in table
-comparison uses =
-uses and & or like python
1) 1 condition:
select _ from table name
where condition;
2) many conditions:
select _ from table name
where condition and/ or condition;
-uses operations for condition
-date after eg. date> 2010- 0- 01
eg.
select * from students
where age>10 and grade = “grade 3”;
delete database
drop database database name;
find sum
select sum (field name) from table name;
eg. select sum (ages) from students;
-only for integers & real
-can add condition but ; is after condition
note: change the way you write capital Y
note: the field names, tables names and data are case sensitive
count
select count (field name) from table name;
eg. select count (firstname) from students;
-displays how many fields in the table
-for all data types
diff btw sum & count
sum - sum of no.s
count - no. items
order by
select */ field name, field name from table name
order by field name ASC/ DESC ;
asc = ascending order, desc = descending order (for letters & no.s)
-after condition
what happens if condition isn’t specified
-checks if there is data in that field & displays the ones with it
-eg. where age;
which is first: condition or order by
condition
order by
