SQL. Credits: Drs. Liu and Raj, Silberschatz , et al . Structured Query Language (SQL). Developed by IBM (system R) in the 1970s Standards (needed due to multiple vendors) SQL-86 SQL-89 (minor revision) SQL-92, aka SQL2 (major revision) Most DBMS in production support this
Credits: Drs. Liu and Raj, Silberschatz, et al.
Check standard supported by your DBMS version
create type Dollars as numeric (12,2) final
create table r(A1D1, A2D2, ..., AnDn,(integrity-constraint1), ..., (integrity-constraintk))
create table branch (branch_namevarchar(15) not null,branch_cityvarchar(30),assets integer);
create table branch (branch_namevarchar(15) not null,branch_cityvarchar(30),assets integer),
primary key (branch_name));
select *from loan
selectloan_number, branch_name, amount 100from loan
would return a relation that is the same as the loan relation, except that the value of the attribute amount is multiplied by 100.
Result is a relation consisting of each instructor concatenated with each of the “teaches”
Instances on that relation
Note that this example is provided in the text on page 69 – and the answer provided
select from borrower, loan
select loan_numberfrom loanwhere branch_name ='Perryridge'and amount > 1200
selectloan_numberfrom loanwhere amountbetween 90000 and 100000
Check your implementation of SQL. “between X and Y” may include X and or Y, or exclude both
old-name as new-name
select customer_name, borrower. loan_numberas loan_id, amountfrom borrower, loanwhere borrower.loan_number = loan.loan_number
select customer_namefrom customerwherecustomer_streetlike '% Main%’
See the book if you want to use a % or _ in the actual pattern
select customer_name, T.loan_number, S.amountfrom borrower as T, loan as Swhere T.loan_number = S.loan_number
Find the names of all branches that have greater assets than some branch located in Brooklyn.
select distinct T.branch_namefrom branch as T, branch as S rename the same relation to do 1-1 comparewhere T.assets > S.assetsand S.branch_city = 'Brooklyn‘
select distinct customer_namefrom borrower, loanwhere borrower.loan_number = loan.loan_number and branch_name = 'Perryridge' order by customer_name
delete from accountwhere branch_name = 'Perryridge'
delete from accountwhere branch_name in (select branch_namefrom branchwhere branch_city = 'Needham')
insert into accountvalues ('A-9732', 'Perryridge', 1200)
or equivalentlyinsert into account (branch_name, balance, account_number)values ('Perryridge', 1200, 'A-9732')
insert into accountvalues ('A-777','Perryridge', null )
update accountset balance = balance 1.06where balance > 10000
updateaccountsetbalance = casewhenbalance <= 10000 thenbalance *1.05elsebalance * 1.06end