sql operations

list databases

-- show databases; -- for mysql
\l -- for postgresql

deleting a database

drop databsae <dbname>;

creating databases

create database <dbname>;

describe database

describe sys;

schema

an example of creating a schema (usually called a database), a schema called COMPANY owned by the user with authorization identifier Jsmith. note that each statement in SQL ends with a semicolon.

CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith';

in general, not all users are authorized to create schemas and schema elements. the privilege to create schemas, tables, and other constructs must be explicitly granted to the relevant user accounts by the system administrator or DBA.
[cite:@elmasri_db_2015 chapter 6.1.1 schema and catalog concepts in sql]

create table

the CREATE TABLE command is used to specify a new relation by giving it a name and specifying its attributes and initial constraints. the attributes are specified first, and each attribute is given a name, a data type to specify its domain of values, and possibly attribute constraints, such as NOT NULL. the key, entity integrity, and referential integrity constraints can be specified within the CREATE TABLE statement after the attributes are declared, or they can be added later using the ALTER TABLE command.
typically, the sql schema (usually called database) in which the relations are declared is implicitly specified in the environment in which the CREATE TABLE statements are executed. alternatively, we can explicitly attach the schema name to the relation name, separated by a period. for example, by writing

CREATE TABLE COMPANY.EMPLOYEE

rather than

CREATE TABLE EMPLOYEE

the relations declared through CREATE TABLE statements are called base tables (or base relations); this means that the table and its rows are actually created and stored as a file by the dbms.

CREATE TABLE EMPLOYEE
 (Fname      VARCHAR(15)   NOT NULL,
  Minit      CHAR,
  Lname      VARCHAR(15)   NOT NULL,
  Ssn        CHAR(9)       NOT NULL,
  Bdate      DATE,
  Address    VARCHAR(30),
  Sex        CHAR,
  Salary     DECIMAL(10,2),
  Super_ssn  CHAR(9),
  Dno        INT           NOT NULL,
 PRIMARY KEY (Ssn), -- has continutation, unclosed
CREATE TABLE DEPARTMENT
 (Dname          VARCHAR(15) NOT NULL,
  Dnumber        INT         NOT NULL,
  Mgr_ssn        CHAR(9)     NOT NULL,
  Mgr_start_date DATE,
  PRIMARY KEY (Dnumber),
  UNIQUE (Dname),
  FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) );
CREATE TABLE DEPT_LOCATIONS
 (Dnumber   INT         NOT NULL,
  Dlocation VARCHAR(15) NOT NULL,
  PRIMARY KEY (Dnumber, Dlocation),
  FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT (Dnumber) );
CREATE TABLE PROJECT
 (Pname VARCHAR(15)      NOT NULL,
  Pnumber   INT          NOT NULL,
  Plocation VARCHAR(15),
  Dnum      INT          NOT NULL,
  PRIMARY KEY (Pnumber),
  UNIQUE (Pname),
  FOREIGN KEY (Dnum) REFERENCES DEPARTMENT (Dnumber) );
CREATE TABLE WORKS_ON
 (Essn  CHAR(9)      NOT NULL,
  Pno   INT          NOT NULL,
  Hours DECIMAL(3,1) NOT NULL,
  PRIMARY KEY (Essn, Pno),
  FOREIGN KEY (Essn) REFERENCES EMPLOYEE (Ssn),
  FOREIGN KEY (Pno) REFERENCES PROJECT (Pnumber) );
CREATE TABLE DEPENDENT
 (Essn           CHAR(9)     NOT NULL,
  Dependent_name VARCHAR(15) NOT NULL,
  Sex            CHAR,
  Bdate          DATE,
  Relationship   VARCHAR(8),
  PRIMARY KEY (Essn, Dependent_name),
  FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) );

[cite:@elmasri_db_2015 chapter 6.1.2 the create table command in sql]

select-from-where

the basic form of the SELECT statement, sometimes called a mapping or a select-from-where block, is formed of the three clauses SELECT, FROM, and WHERE and has the following form:

SELECT <attribute list>
FROM   <table list>
WHERE  <condition>;

where

<attribute list> is a list of attribute names whose values are to be retrieved by the query.
<table list> is a list of the relation names required to process the query.
<condition> is a conditional expression that identifies the tuples to be retrieved by the query.

[cite:@elmasri_db_2015 chapter 6.3.1 the select-from-where structure of basic sql queries]

ordering of query results

the order by operator