Database Administration

Question 1

How do you delete a database or table?

Answer 1

drop database database_name ;
drop table table_name ;

Question 2

What is the syntax for the creation of a table in SQL?

Answer 2

create table table_name(
column_name datatype constraints,
column_name datatype constraints,
column_name datatype constraints
);

Question 3

How do declare a foreign key in SQL?

Answer 3

create the table like normal with all the attributes but before closing add the following syntax:
fk_foreign_table_name
foreign key (foreign_key_name) references foreign_key_og_table_name (key_to_reference_name)
then close the whole query

Question 4

How do you select all the data from a single column but avoid duplication?

Answer 4

select distinct column_name form table_name ;

Question 5

How do you insert data into a table in SQL?

Answer 5

insert into table_name (column_1, column_2, column_3,……) values
(val_1, val_2, val_3,……),
(val_1, val_2, val_3,……),
(val_1, val_2, val_3,……),
(val_1, val_2, val_3,……),
;

Question 5

How do you write dates in SQL?

Answer 5

“year-month-day”

Question 6

How are boolean operations written in SQL?

Answer 6

or, and

Question 7

How do you select data within a certain date range in SQL?

Answer 7

date >= “date”
then use the “and” operator to specify limits
-> to specify only the year:
year(date_column_name) >= year_as_int

Question 8

How do you create a join in SQL?

Answer 8

select table_name.column, tablen_ame.column
from og_table
join foreign_table on og_table.foreing_key = foreign_table.primary_key

Question 9

How do you set default values when creating a table in SQL?

Answer 9

create table table_name(
column_name data_type default default_value
) ;

Question 10

How are floats declared in SQL?

Answer 10

decimal(number_of_total_digits, number_of_decimal_points)

Question 11

How do you join two columns as one in a query?

Answer 11

concat(column1, “”, column2) as temp_col_name

Question 12

How do you return only a certain percentage of column in SQL?

Answer 12

(math_operation on column_name) as temp_name

Question 13

How do you sort query results in ascending or descending order?

Answer 13

select * from table_name order by column_name (asc/desc)

Question 14

How many normal forms exist and how would you describe the first 3?

Answer 14

First, second, Third, and Boyce code normal form.

First normal form: This normal form aims to reduce redundancy by ensuring that all column data is atomic or a single value

Second normal form: This normal form aims at reducing redundancy by ensuring that every non-key on a table is fully dependent on the primary key

Third normal form: This normal form aims at reducing redundancy by eliminating transitive dependencies, meaning no non-key attribute can depend on another non-key attribute

Question 15

what is normalisation?

Answer 15

Normalization is breaking down a database into small, manageable parts with the aim of reducing database redundancy.

Question 16

What is a database anomaly?

Answer 16

It is an undesired database behavior, usually caused by poor database organization.

Question 17

Explain deletion, update, and insertion anomalies.

Answer 17

Deletion anomaly:
It is a database anomaly that occurs when the deletion of a record, causes the intentional deletion of some other piece of data.

Update anomaly:
It is an anomaly that occurs when updating database data leads to inconsistencies or unintended changes across multiple records.

Insertion anomaly:
This is an anomaly that occurs when the insertion of new data into the database also requires the insertion of some piece of unrelated data.

Question 18

How do you sum all the values in a column?

Answer 18

SUM(column_name)

Question 19

How do you count all the records in a table using SQL?

Answer 19

count(*)

Question 20

How do you get the average value in a column?

Answer 20

where column_name > (select avg(column_name) from table_name) ;

Question 21

How do you update a record in SQL?

Answer 21

update table_name
set column_1 = val, colun_2 = val
where condition ;

Question 22

How do you query the names of all records whose attributes start or end with a given letter in SQL?

Answer 22

start:
where att_name like “letter%” ;
end:
where att_name like “%letter” ;
start and end:
where att_name like “%letter% ;

Question 23

Name one reserved word in SQL.

Answer 23

order