BIS II - SQL

Question 1

SQL

- Definition & use

Answer 1

Structured Query Language
Standard relational database
Used to 
- define and set up relations
- ton insert, alter, and to delete data 
- to query data from one or more relations

Question 2

Data Types

Tinyint

Answer 2

Binary variable (representing yes/no decisions)

Question 3

int(m)

Answer 3

Integer numbers with an m maximum displayed places

Question 4

float(m,d)

Answer 4

Float numbers with an m maximum displayed places and d decimal places

Question 5

char(m)

Answer 5

Character string of length m

Question 6

varchar(m)

Answer 6

character string of a variable length (m: maximum length)

Question 7

date

Answer 7

Date in format YYYY-MM-DD

Question 8

Creating a table

Answer 8

CREATE TABLE Customer 
(
Customer_id INT NOT NULL, 
Name VARCHAR(40) NOT NULL,
PRIMARY KEY (Customer_id)
)

Question 9

Dropping a Table

Answer 9

DROP TABLE customer (can also be in lower case)

Question 10

Adding new attributes

Answer 10

ALTER TABLE Customer ADD rating INT NOT NULL AFTER Discount`

Question 11

Dropping attributes

Answer 11

ALTER TABLE Customer DROP rating

Question 12

Inserting new data records

Answer 12

INSERT INTO Customer (Customer_id, Name, Address) VALUES (NULL, James Bond, Burgplatz 2`);

Question 13

Altering data records

Answer 13

UPDATE Customer SET Discount= ‘0.08’ WHERE Customer.Customer_id= 1

Question 14

Selecting an entire table

Answer 14

SELECT * FROM Customer

Question 15

Selecting from a table

Answer 15

SELECT Name, Address FROM Customer

Question 16

Selecting from a table with constraints

Answer 16

SELECT Customer_id, Name, Address FROM Customer WHERE rating=4

Question 17

Selecting data, additional clauses to WHERE

Answer 17

AND: displays a record if all the conditions separated by AND are true
OR displays a record if any of the conditions separated by OR is true
NOT displays a record if the condition(s) is NOT TRUE

Question 18

Selecting Data: Combining rows

Answer 18

SELECT Sales_transaction.Customer_id, Customer.Customer_name FROM Sales_transaction JOIN Customer On Sales_transaction.Customer_id=Customer.Customer_id