Chap 9 - databases, SQL

Question 1

define database

Answer 1

-structured collection of data that can be added, remved, changed or updated
-can have many tables in it

Question 2

3 uses of storing on a databases & eg

Answer 2

1) people info
-patients in hospital
-students in skl
2) things info
-books in library
-cars sold
3) events info
-hotel booking
race results

Question 3

define flat files

Answer 3

a simple table with fields and records and it not related to other databases

Question 4

the problem with flat files

Answer 4

when data is changed in a file, it is not reflected in another
-causes confusion & inconsistency

Question 5

solution to flat file (advantages over them)

Answer 5

-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

Question 6

format of a database

Answer 6

-tabular structure
-field - table with 1 type of info
records - row of all data on a person/ thing
field field field
info info info

Question 7

data types in tables compared to python

Answer 7

text - string
character - character
boolean - boolean
integer - integer
real - float
data/ time eg. 2/2/12

Question 8

uses of data types

Answer 8

-all field need a data type to classify how to store, display and perform operation on it

Question 9

define primary key & its use

Answer 9

-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

Question 10

how do you get a foreign key

Answer 10

-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

Question 11

what would happen without primary key

Answer 11

-insertion issues - rows are uniquely identified
-updating issues

Question 12

what is SQL

Answer 12

Structed Query Language

Question 13

note: about SQL
-case insensitive
-no need indentation
- , separates items
-end each command with ;
-anything that is text, except command words, in “ “

Question 14

command words for SQL

Answer 14

-create database
-drop database
-create table
-insert into
-select ___ from
-where
-sum
-count
-order by

Question 15

how to create database

Answer 15

create database database name;

Question 16

how to create table

Answer 16

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 ;

Question 17

how to write data types in SQL & limit

Answer 17

text - varchar (250) - max limit is 255 so it doesn’t go over 1 byte
integer - int - no limit

Question 18

how to insert data in table

Answer 18

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

Question 19

how to select data in table

Answer 19

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;

Question 20

how to select data based on condition in table
-comparison uses =
-uses and & or like python

Answer 20

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”;

Question 21

how to delete database

Answer 21

drop database database name;

Question 22

how to find sum

Answer 22

select sum (field name) from table name;
eg. select sum (ages) from students;
-only for integers & real
-can add condition but ; is after condition

Question 23

note: change the way you write capital Y

Question 24

note: the field names, tables names and data are case sensitive

Question 25

how to count

Answer 25

select count (field name) from table name;
eg. select count (firstname) from students;
-displays how many fields in the table
-for all data types

Question 26

diff btw sum & count

Answer 26

sum - sum of no.s
count - no. items

Question 27

how to use order by

Answer 27

select _ from students
order by field name asc/ desc ;
asc = ascending order, desc = descending order (for letters & no.s)
-after condition

Question 27

what happens if condition isn’t specified

Answer 27

-checks if there is data in that field & displays the ones with it
-eg. where age;

Question 28

which is first: condition or order by

Answer 28

condition
order by