Summary Comprehensive Databases Cheat Sheet

Min #elmts to delete

to
reduceht a y



want to delete entries from
lowest layer of interior nodes.




First identifynodeatheight- a
-


nodes. Then , remove a
key
from the leaf w/the
fewest e n t r i e s

00 00
ownbutterpoo
DBsystems typically implementtheir
as ma ,
a




Theymay need to scaletoobjectslarger
-
than memory ce i s



issues
-



variations in OS support
-




portability
Need to in memor y
to be able pages
-




pin
replacement pol a
canget betterp erformance
by tailoring
·




would NOT implement
An example of when a DB system

requirese
isforsystemslikespite where minimizingprouting te




TERE M dname .
=
'Comp Scil




For eachMathematisclass a
pin
s

Foeachdeptthat has one ormajorsWP e SELECT T cho T Secto COUNT (E sid)
gpa)
.
.




SELECT DISTINCT
,

M dname
.




AvG(S FROM
,
.




,
.



major M Print course names
,
course numbers ,
FROM enroll E
JOIN Student S M Sid S Sid and section numbers
on .
= .




of all classes
JOIN Student S on E sid
.
=
S sid
.




WHERE M dname IN ( with >3 female students enrolled.
JOIN Section T T dname E.dname
.

on .
: AND. . .
FROM
SELECT M-dname major M Names of all musicians SELECT C name , C Cno
.
.




,
E Secto
.
FROM course ( WHERE E dname
.
=
Mathematics' AND S gpa < 3 .




JOIN Student S M Sid S Sid JOIN enroll E AND .
born insame is C GROUP BY T Cno T
Sectno-Rel algebrae
on =
on E CnO
Avg# of
city Cno =



per officer ?
.




Max sustained allegation
.
. - -




TRRs
. .




,
.




count
WHERE S gpa < 1 0) Esid
JOINStudents
S i
on
. .
=


for officers
min andmax?Inaa
GROUP BY M dname ; in each rank ? Sor t
SELECT M Number of sailors that reserved

GROUBYEseRealga
name
.




m
desc
-
.




results in order each red boat
FROM musician M
,
-




who is newest
officer the force ? SELECT AVG(tor-count), (DISTINCT Sid
on
musician 3
SELECTrankmax(sustained To
n SELECT B bid COUNT
.




first-name
MINCEvr-countT
,
SELECT
middle-initial last n a m e WHERE J "John Evans' Boats B
, , .
m - name =
FROM
FROM data-officer GROUP By rank AND J bor n-in

JONReservesRONBid
M born-in =
= Rod is
(
. .




FROM
WHERE
appointed-date ( =
ORDER By max(sustained-count) DESC ; ORDER BY M m-name DESC ;
COUNT(t id)
.




SELECT AS tri-count
SELECT
MAX(appointed-date Name of hospital w/highest ava patient age
.




( 18) bid
FROM data-officer O GROUP BY B
=



FROM data-officer
.




SELECT

LEFTJON -ErTon Toid-O
h name FROM patient P treated by T doctor D hospital H
NULL)
-




WHERE
, ,



appointed-date
,

IS NOT
WHEREPpid =TpidANDTdidDdidAND Did Hid AND Page As
:




ORDER BY id ;