CH 2 - SQL

Question 1

SQL

Answer 1

structured query language. its the ANSI language use for defintion and manipulation of relational database

Question 2

ddl

Answer 2

data definition language. statements that specify and modify database schemas.

e.g
CREATE TABLE
DROP TABLE. removes a table from databse
ALTER TABLE. modifies a table after created
CREATE INDEX

Question 3

dml

Answer 3

data manipulation language. statements that manipulate databse content.

eg
INSERT
UPDATE
DELETE

Question 4

facts of sql

Answer 4

sql data is case sensitive while commands are not .

Question 5

create table example

Answer 5

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Gender CHAR(1),
Email VARCHAR(100)
);

Question 6

e.g of alter a table or drop

Answer 6

ALTER TABLE Student
ADD PhoneNumber VARCHAR(15);

DROP TABLE Student;
to avoid errors: DROP TABLE IF EXISTS Student;

Question 7

explain the 2 drop options

Answer 7

CASCADE : specifies that any foreign key constraint violations that are caused by dropping the table will cause the correspnding rows of the related table to be deleted.

RESTRICT : blocks the deletion of the table of any foreign key constraint violations would be created.

Syntax;
DROP TABLE <table name>[RESTRICT | CASCADE];

learn syntax

Question 8

DML COMMANDS

Answer 8

INSERT: adds new rows to tables
UPDATE: modifies one or more attributes
DELETE: deletes one or more rows from a table

Question 9

e.g of instering a row

Answer 9

INSERT into<table name>
VALUES(‘value 1’ , ‘value2’ , NULL);

INSERT into FoodCart
VALUES(‘02/08/24’ , ‘ pizza’,’70’)

Question 10

e.g of updating an attribute with sql

Answer 10

UPDATE <table name> SET <attr> = <value>
WHERE <selection>;</selection></value></attr>

e.g

UPDATE FoodCart SET sold = 349
WHERE date = ‘02/25/24’ AND food = ‘pizza’;

Question 11

DELETE a row in a table e.g

Answer 11

DELETE FROM <Table name>
WHERE <condition>;</condition>

DELETE FROM FoodCart
WHERE food = ‘hotdog’;

Question 12

SQL statement

Answer 12

basic SELECT statement includes 3 clauses

SELECT <attribute> FROM <tables> WHERE <condition></condition></tables></attribute>

select specifies the attributes that are part of the resulting relation

FROM = specifies the tables that serve as the input to the statement

WHERE = specifies the selection condition , including the join condition.

// is it possible to use this without WHERE condition?

nb// * in the select statement means that every attribute of the input table is to be selected.

SELECT * FROM …WHERE….;

to get unique rows use DISTINCT keyword after SELECT e.g
SELECT DISTINCT * FROM …
WHERE …;

Question 13

SQL operations

Answer 13

join
left join
right join
like

Question 14

sql clauses

Answer 14

order by

Group by

Having

Question 15

Join operation

Answer 15

its specified as the FROM clause which list the two inut relations and the WHERE clause which lists the join condition

Question 16

e.g of inner join

Answer 16

SELECT *
FROM emp join dept
on emp.id = dept.id;

Question 17

left outer join e,g

Answer 17

left outer join = left join

SELECT *
FROM emp left join dept
on emp.id = dept.id ;

Question 18

right join e.g

Answer 18

right outer join = right join