Chapter 2 - SQL

Question 1

What is SQL and what is it’s advantage?

Answer 1

Structured Query Language.
Previously Known as Structured English Query Language.

Advantage: simple and declarative so user don’t have to think much. Software can do operation optimisation

Question 2

What syntax must u add after the SQL statement?

Answer 2

must add ;

Question 3

How are comments done in SQL?

Answer 3

using –
or
using /* line 1 line 2 */

Question 4

Naming convention for SQL

Answer 4

First character should be a letter
Uppercase and lowercase for different letters in one name
use underscore to connect different words
consistency: follow the same naming schemes for a whole project

Question 5

Data types for SQL

Answer 5

Data types
* char(n). Fixed length character string, with user-specified length n.
* varchar(n). Variable length character strings, with user-specified maximum
length n.
* int. Integer (a finite subset of the integers that is machine-dependent).
* smallint. Small integer (a machine-dependent subset of the integer domain
type).
* numeric(p,d). Fixed point number, with user-specified precision of p digits, with
d digits to the right of decimal point. (ex., numeric(3,1), allows 44.5 to be stores
exactly, but not 444.5 or 0.32)
* real, double precision. Floating point and double-precision floating point
numbers, with machine-dependent precision.
* float(n). Floating point number, with user-specified precision of at least n digits.
* date, time, timestamp. For values contains date and time

Question 6

How to perform SQL Select statement?

Answer 6

Select A1,A2
From R
where P

A is an attribute
R is a relation
P is a predicate

Question 7

How to perform SQL Insert statement?

Answer 7

insert into R values(v1,v2,v3)
E.g. Insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000);

Question 8

How to perform SQL delete?

Answer 8

Delete from R
e.g. delete from student

Question 9

How to perform SQL create table?

Answer 9

CREATE TABLE TableName (
attribute1 type1 constraints1,
attribute2 type2 constraints2,
..
table-level constraints, …)

Create a new table in a database
CREATE TABLE Students (
sid serial,
name varchar(40),
dob date ,
gender char(1) CHECK (gender in (‘M’,’F’)),
degree integer
);
* check: checks whether an assertion is true, limit the value range
* Tables are removed via: DROP TABLE table_name

Question 10

What is DDL and DML and what are their differences?

Answer 10

Data Definition Language: (CREATE, ALTER, DROP) TABLE

Data Manipulation Language: (SELECT, INSERT, UPDATE, DELETE)