SQL Exercises

Question 1

Database, Database Management System and Database System

Answer 1

DB = collection of related data
DBMS = provides a set of services to create, access, maintain databases
DBS = DBMS + DB

Question 2

Information and Data

Answer 2

Data are of recorded facts and numbers only

Infomation is data presented in a meaningful context and processed by aggregating, comparing or similar operations

Question 3

Referential Integrity

Answer 3

Referential integrity means that each value of the foreign key has to exist as a value of the primary key in the referenced table.

A DBMS ensures that

• no new can be entered without an existing customer number
• no costumer data can be deleted as long as there are orders
• no customer IDs can be changed without changing the dependent order tuples

Question 4

Keys

Answer 4

Super Key:
- identifies each tuple (row) of the relation uniquely. It can consist of one or more attributes

Candidate Key:
- a super key with minimal attributes

Primary Key:
- the chosen Candidate Key to identify a tuple in the relation

Foreign Key:
- attribute in a relation that matches the primary key of another table. It can be used to cross-reference relations

Question 5

Create a table

Answer 5

CREATE TABLE tableName
(
column1 datatype [default value/ constraint],
column 2 datatype [default value/ constraint],
… … …
);

Do not forget the paranthesis and semicolon

Question 6

Data Type

Answer 6

Integer/ int:
- Integer approc +- 2.147.483.000

Decimal (x,y)/ Dec (x,y):
- Formatted number mit x decimal digits that can be stored in total (precision) and y decimal digits right of the decimal point (scale)

Date:
- Date
Time:
- Time
Datetime:
- Date and Time

Character (n)/ char (n):
- Text with a length of exactly n characters, fixed length

Varchar (n):
Text with a mx. length of n characters, variable length

Use quotation marks for Char and Varchar

Question 7

Insert data into a table

Answer 7

INSERT INTO tableName
VALUES
(
values in the right order!
);

Question 8

Select data from a table

Answer 8

SELECT columnName
FROM tableName
WHERE columnName = condition
;

Question 9

Join data from tables

Answer 9

SELECT t1N.columnName, t2N.columnName, t3N.columnName

FROM table1Name t1N
JOIN table2Name t2N
ON t1N.columnName = t2N.columnName (condition)

JOIN table3Name t3N
ON t2N.columnName = t3N.columnName (any condition)

WHERE columnName = condition
;

Question 10

Giving out the Sum of some amounts

Answer 10

SELECT SUM(amount1)
FROM
WHERE

Question 11

Giving out the number of entry that fulfill a certain condition

Answer 11

SELECT COUNT(*)
FROM
WHERE

Question 12

Selecting an entry where an entry fulfills a condition and another condition which is linked to a function

Answer 12

SELECT 
FROM
WHERE condition 1
AND condition 2 = SELECT MAX()
FROM
WHERE

Question 13

Ordering entrys

Answer 13

After the query you put

ORDER BY entry ASC or DESC

Question 14

Searching texts in entry for certain characters or words

Answer 14

WHERE column LIKE ‘….’
% means anything can come before and after
_ one character, in front or behind it

Question 15

Selecting values which are not in a table

Answer 15

WHERE column IS NOT (SELECT column FROM table)

Question 16

Daten ordnen nachdem man die Anzahl von Zellen gezählt hat oder die Summe vom Inhalt

Answer 16

GROUP BY

Example
SELECT column1; COUNT(column2); SUM(column3)
FROM
GROUP BY column1

Gibt an wie viele Einträge von column2 verhanden sind in der Reihe von column1 und die Summe der Einträge von in column3.

Question 17

Spalten in der ausgegeben Tabelle mitbestimmten Namen versehen

Answer 17

SELECT coumn1 AS name
FROM

Example
SELECT name AS friend