Databases and SQl

Question 1

What are the data types to choose from when creating database fields?

Answer 1

⚫️integer
⚫️float
⚫️date / time /Datetime
⚫️Char (FIXED length up to 8,000 characters)
⚫️Varchar (variable length string up to 8,000 characters )
⚫️Text (variable length string up to 2GB)
⚫️autonumber

Question 2

What are flat file databases?

Answer 2

Stores sa single table of data inside a single text file

Question 3

What are primary keys ?

Answer 3

A field that stores unique data for each record in a table.

Question 4

What are some flat file database issues ?

Answer 4

🔵inconsistencies in the data , making it hard to search or sort the data
🔵redundant data, so the databases use more storage/memory than needed

Question 5

What is a ‘flat file database “?

Answer 5

A database that has all the data stored in one table.
▪️Each record is identified as a new line
▪️flat file databases are often stored using a CSV (comma separated values )format
▪️each field is separated by a comma

Question 6

What are relational databases ?

Answer 6

Contain multiple tables , each table is known as a relation. Each table has links to others known as relations.

Question 7

What is a foreign key?

Answer 7

A field in a table that references the primary key of another table.

Question 8

What is SQL?

Answer 8

Structured query language
The language allows you to create , query, update and delete data to and from databases

Question 9

Write an SQL query to output all the first names of members and their memberID, who are male

Answer 9

• all normal PSEUDOCODE arithmetic and operational comparatives can be used

Question 10

How do you select all the fields within a table to output

Answer 10

SELECT *

Wildcard

Question 11

Write an SQL query to find all the fields of dogs that are older than four (including those that are 4) and whose colour contains the letter ‘B’

Answer 11

Question 12

Sort this array called members into ascending order of town , up outputting the first name and memberID

Answer 12

Question 13

Change the age of Molly from 5 to 6

Answer 13

Question 14

Delete all the black spaniels from the table

Answer 14

Question 15

Select the owner and dog names for all the four year old dogs
(Multiple tables )

Answer 15

Question 16

Why are databases used?

Answer 16

They are used to store large amounts of data.
They make retrieving information easy and efficient

Question 17

What are some advantages of flat file databases ?

Answer 17

All data in one table , so easier to set up and understand

Question 18

What are some advantages of relational databases ?

Answer 18

🔵data is only stored in one place and then referenced. This means that to input , update or delete pieces of data, it only has to be done in one place which can prevent inconsistencies
🔵only storing the same price of dat once will also save storage space
🔵relational databases can be more secure , some tables can be made more confidential so only some users can see certain data (helps companies meet data protection laws)

Question 19

What is a data redundancy?

Answer 19

Having the same piece of data stored in multiple places

Question 20

How do you insert a full record into a table ?

Answer 20

Question 21

How would insert bits of a record into a table ?

Answer 21

Question 22

What is a table ?

Question 23

What is a record ?

Answer 23

The row of a table

Question 24

What is a field ?

Answer 24

Columns of the table

Question 25

How do relational databases eliminate data inconsistencies?

Answer 25

Since the data only has to be entered once, and can be referenced through other tables, there is less likely to be any inconsistencies such as spelling mistakes made when entering data in. This will reduce the amount of errors made when making queries

Question 26

How do relational databases eliminate data redundancy?

Answer 26

Since the data can be referenced from other tables using foreign keys, the same pieces of data do not have to be repeated and stored in multiple places, there are no longer pieces of data that are being repeated unnecessarily