Please wait
A cheat sheet for computer science students.
The code style is user-friendly.
\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}
Our gallery is the easiest way to put your LaTeX templates, examples and articles online. Just create a free project and use the publish menu. It only takes a couple of clicks!
The LaTeX templates, examples and articles in the Overleaf gallery all come from our amazing community.
New content is added all the time. Follow us on twitter for the highlights!