A cheat sheet for computer science students.
The code style is user-friendly.
\begin
Discover why 18 million people worldwide trust Overleaf with their work.
\documentclass[10pt,landscape]{article}
\usepackage{multicol}
\usepackage{calc}
\usepackage{ifthen}
\usepackage[landscape]{geometry}
\usepackage{amsmath,amsthm,amsfonts,amssymb}
\usepackage{color,graphicx,overpic}
\usepackage{hyperref}
\usepackage{listings}
\lstset{
basicstyle=\ttfamily,
columns=fullflexible,
frame=single,
breaklines=true,
postbreak=\mbox{\textcolor{red}{$\hookrightarrow$}\space},
}
\pdfinfo{
/Title (example.pdf)
/Creator (TeX)
/Producer (pdfTeX 1.40.0)
/Author (Seamus)
/Subject (Example)
/Keywords (pdflatex, latex,pdftex,tex)}
% This sets page margins to .5 inch if using letter paper, and to 1cm
% if using A4 paper. (This probably isn't strictly necessary.)
% If using another size paper, use default 1cm margins.
\ifthenelse{\lengthtest { \paperwidth = 11in}}
{ \geometry{top=.5in,left=.5in,right=.5in,bottom=.5in} }
{\ifthenelse{ \lengthtest{ \paperwidth = 297mm}}
{\geometry{top=1cm,left=1cm,right=1cm,bottom=1cm} }
{\geometry{top=1cm,left=1cm,right=1cm,bottom=1cm} }
}
% Turn off header and footer
\pagestyle{empty}
% Redefine section commands to use less space
\makeatletter
\renewcommand{\section}{\@startsection{section}{1}{0mm}%
{-1ex plus -.5ex minus -.2ex}%
{0.5ex plus .2ex}%x
{\normalfont\large\bfseries}}
\renewcommand{\subsection}{\@startsection{subsection}{2}{0mm}%
{-1explus -.5ex minus -.2ex}%
{0.5ex plus .2ex}%
{\normalfont\normalsize\bfseries}}
\renewcommand{\subsubsection}{\@startsection{subsubsection}{3}{0mm}%
{-1ex plus -.5ex minus -.2ex}%
{1ex plus .2ex}%
{\normalfont\small\bfseries}}
\makeatother
% Define BibTeX command
\def\BibTeX{{\rm B\kern-.05em{\sc i\kern-.025em b}\kern-.08em
T\kern-.1667em\lower.7ex\hbox{E}\kern-.125emX}}
% Don't print section numbers
\setcounter{secnumdepth}{0}
\setlength{\parindent}{0pt}
\setlength{\parskip}{0pt plus 0.5ex}
%My Environments
\newtheorem{example}[section]{Example}
% -----------------------------------------------------------------------
\begin{document}
\raggedright
\footnotesize
\begin{multicols}{3}
% multicol parameters
% These lengths are set only within the two main columns
%\setlength{\columnseprule}{0.25pt}
\setlength{\premulticols}{1pt}
\setlength{\postmulticols}{1pt}
\setlength{\multicolsep}{1pt}
\setlength{\columnsep}{2pt}
\begin{center}
\Large{\underline{COMS4111}} \\
\end{center}
\section{Piazza Resources}
Relevant: Chapter 4, sections 1-6 of "Database Systems -- The Complete Book"
\subsection*{Sample question 1}
\subsubsection*{CREATE Syntax}
\begin{lstlisting}[language=SQL]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
\end{lstlisting}
create\_definition:
\begin{lstlisting}[language=SQL]
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...)
[index_option] ...
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
| CHECK (expr)
\end{lstlisting}
column\_definition:
\begin{lstlisting}[language=SQL]
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
\end{lstlisting}
Create Table Using Another Table
\begin{lstlisting}[language=SQL]
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;
\end{lstlisting}
\subsubsection*{Select Syntax}
\begin{lstlisting}[language=SQL]
SELECT col1, col2, col3, ..., FROM table1
WHERE col4 = 1 AND col5 = 2
GROUP BY … # aggregate the data
HAVING count(*) > 1 # limit aggregated data
ORDER BY col2
\end{lstlisting}
\subsubsection*{Insert Syntax}
\begin{lstlisting}[language=SQL]
# insert values manually
INSERT INTO table1 (ID, FIRST_NAME, LAST_NAME)
VALUES (1, ‘Rebel’, ‘Labs’);
# or by using the results of a query
INSERT INTO table1 (ID, FIRST_NAME, LAST_NAME)
SELECT id, last_name, first_name FROM table2
\end{lstlisting}
\subsubsection*{Alter table Syntax}
\begin{lstlisting}[language=SQL]
# add a column
alter table tablename
add column_name datatype
# drop a column
alter table tablename
drop col1, drop col2
# alter a column's datatype
alter table tablename
alter column column_name datatype / modify column column_name datatype auto_increment
# add a constraint
alter table tablename
add constraint fk foreign key (col) references tblname2(col2)
\end{lstlisting}
\subsubsection*{Update Syntax}
\begin{lstlisting}[language=SQL]
UPDATE table1
SET table1col1 = 1
FROM table2
WHERE col2 = 2
\end{lstlisting}
\subsubsection*{Create index Syntax}
\begin{lstlisting}[language=SQL]
create index idx_name
on table_name(col)
\end{lstlisting}
\subsubsection*{Constraint Syntax}
\subsubsection*{Sample Solution 1}
\begin{enumerate}
\setcounter{enumi}{1}
\item
\begin{lstlisting}[language=SQL]
create table addresses (
id int auto_increment unique not null,
street_name varchar(100) not null,
city varchar(20) not null,
region varchar(20),
postal_code int(5),
country varchar(20) not null,
constraint uc_address unique(street_name, city, region, postal_code, country),
constraint pk_addresses primary key(id)
)
\end{lstlisting}
\item
\begin{lstlisting}[language=SQL]
insert addresses(addresses, city, region, postal_code, country)
select addresses, city, region, postal_code, country
from customers
group by addresses, city, region, postal_code, country
OR
insert into addresses(...)
select distinct address, city, region, postal_code, country from customers
\end{lstlisting}
\item
\begin{lstlisting}[language=SQL]
# Clone a table
create table new_customers like customers
# Load the data
insert into new_customers
select * from customers
\end{lstlisting}
\item
\begin{lstlisting}[language=SQL]
alter table new_customers
add column address_id int
\end{lstlisting}
\item
\begin{lstlisting}[language=SQL]
update table new_customers
set new_customers.address_id = addresses.id
from new_customers join addresses on
new_customers.address = addresses.street_name
and new_customers.city = addresses.city
and new_customers.region = addresses.region
and new_customers.postal_code = addresses.postal_code
and new_customers.country = addresses.country
OR
`1upda `Q1E4RTYU7IOP[-\te new_customers
set address_id=(select id from address where
(...AND (... OR (adrresses.region is null or new_customers.region os null)))
\end{lstlisting}
\item
\begin{lstlisting}[language=SQL]
alter table addresses
drop column street_name,
drop city,
drop region,
drop postal_code,
drop country
\end{lstlisting}
\item
\begin{lstlisting}[language=SQL]
alter table new_customers
add constraint fk_new_customers_addresses foreign key (address_id)
references addresses(id);
create index idx_new_customer_id on
new_customers(id)
\end{lstlisting}
\end{enumerate}
\subsection*{Sample question 2}
Define a datamodel using the notation we used in class. You should define either a logical or physical model. From your model, create the SQL DDL.
The datamodel is the following.
\begin{itemize}
\item Companies: These are businesses and have properties Name, ID. The ID should be unique and derived from the company name. Companies also have an address.
\item Persons: A Person has a last\_name, first\_name and middle initial. A Person also has an email. A Person also has an address.
\item A Company may be related to one or more Persons. A Person may be related to one or more Companies. A Person-Company relationship has a type, e.g. Employee, Contractor, Consultant. The data model should ONLY allow creation of relationships that conform to one of the types. It must be possible to add new, named types.
\end{itemize}
\subsubsection*{Trigger Syntax}
% \begin{lstlisting}
% CREATE
% [DEFINER = { user | CURRENT_USER }]
% TRIGGER trigger_name
% trigger_time trigger_event
% ON tbl_name FOR EACH ROW
% [trigger_order]
% trigger_body
% trigger_time: { BEFORE | AFTER }
% trigger_event: { INSERT | UPDATE | DELETE }
% trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
% \end{lstlisting}
Example:
\begin{lstlisting}
create definer = `root` @ `localhost` trigger `University`.`enrollment_before_insert` before insert on `enrollment` for each row
begin
declare new_day varchar(1);
declare new_start int;
declare new_end int;
select sections.
\end{lstlisting}
\subsubsection*{Function Syntax}
\begin{lstlisting}[language=SQL]
create function function_name(param1, param2 ...)
returns datatype
[not] deterministic
statements
\end{lstlisting}
Example:
\begin{lstlisting}
drop function if exists customerlevel;
DELIMITER $$
CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE lvl varchar(10);
IF p_creditLimit > 50000 THEN
SET lvl = 'PLATINUM';
ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
SET lvl = 'GOLD';
ELSEIF p_creditLimit < 10000 THEN
SET lvl = 'SILVER';
END IF;
RETURN (lvl);
END
create definer='root'@'localhost' trigger 'University'.'person1_BEFORE_INSERT' before insert on 'person1' for each row
begin
set new.uni = generate_uni_1(new.last_name, new.first_name)
end
create definer='root'@'localhost' function 'generate_uni'(last_name varchar(32), first_name varchar(32)) returns varchar(8) charset utf8
begin
declare c1 char(2);
declare c2 char(2);
declare prefix char(5);
declare uniCount int;
declare newUni varchar(6);
set c1 = upper(substr(last_name, 1, 2));
set c2 = upper(substr(first_name, 1, 2));
set prefix = concat(c1, c2, '%');
select count(uni) into uniCount from person1 where uni like prefix;
set newUni = concat(c1, c2, uniCount);
return newUni;
end
\end{lstlisting}
\subsection*{Sample Solution 2}
\begin{lstlisting}[language=SQL]
# Create companies
create table companies(
id varchar(6) not null,
name varchar(100) not null,
address varchar(200),
constraint pk_companies primary key(id)
)
delimiter $$
create definer='root'@'localhost' trigger 'companies'.'companies_before_insert' before insert on companies for each row
begin
set new.id = generate_id(new.name);
end$$
create function generate_id (name varchar(100))
returns varchar(6)
BEGIN
declare prefix varchar(3);
declare idcount int(3);
declare newId varchar(6);
set prefix = upper(substr(name, 1, 3));
select count(id) into idcount from companies where id like prefix;
set newId = concat(prefix, idcount);
return newId;
END $$
delimiter ;
# Use the procedure to create person
create table persons(
id int not null auto_increment
last_name varchar(20) not null,
first_name varchar(20) not null,
initial varchar(2),
email varchar(100),
address varchar(100),
typeid int,
constraint pk_persons primary key (id),
constriant fk_persons_type foreign key(typeid)
);
delimiter $$
create trigger persons_before_insert before insert on persons for each row
begin
call procedure(new.last_name, new.first_name, @initial);
set new.initial = @initial;
end$$
create procedure generate_initial(in last_name varchar(20) first_name varchar(20) ,out initial varchar(2))
begin
set initial = concat(upper(substr(last_name, 1, 1)), upper(substr(first_name, 1, 1)))
end$$
delimiter ;
\end{lstlisting}
\subsection*{Sample Question 3}
\subsection*{Sample Solution 3}
\begin{lstlisting}
TO COPY DON's CODE
create view valid1 as select *
from
(select dish as dish1 from appetizers) as a1 join
(select dish as dish2 from appetizers) as a
create view validorder as
select * from valid1
union
select * from valid2
union
select * from valid3
create procedure
create function # function can update data like insert
create trigger
\end{lstlisting}
% You can even have references
\rule{0.3\linewidth}{0.25pt}
\scriptsize
\bibliographystyle{abstract}
\bibliography{refFile}
\end{multicols}
\end{document}