Sql (Structured Query Language) is a computer language aimed to store, manipulate, and retrieve data
Download 1.42 Mb. Pdf ko'rish
|
sm-sql-150423084832-conversion-gate01
- Bu sahifa navigatsiya:
- Data-definition language (DDL).
- View definition.
- Embedded SQL and dynamic SQL.
- Schema Definition in SQL Create table : Example
- Delete
- Where Clause
- Rename Operation
- Ordering the Display of Tuples
- Set Operations
- Aggregate Functions avg, min, max, sum, count
- Nested Subqueries
- Set Comparison : Example
- Complex Queries
- Modification of the Database Deletion
- Joined Relations Joined Relations
|
INTRODUCTION SQL (Structured Query Language) is a computer language aimed to store, manipulate, and retrieve data stored in relational databases. IBM implemented the language, originally called Sequel, as part of the System R project in the early 1970s..
The first commercial relational database was released by Relational Software later becoming oracle.
INTRODUCTION The SQL language has several parts:
commands for defining relation schemas, deleting relations, and modifying relation schemas.
includes also commands to insert tuples into, delete
views.
specifying the beginning and ending of transactions. INTRODUCTION
statements can be embedded within general-purpose programming languages, such as C, C++, Java, PL/I, Cobol, Pascal, and Fortran.
specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.
commands for specifying access rights to relations and views.
DDL DDL - Data Definition Language: Statements used to define the database structure or schema. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database RENAME - rename an object Schema Definition in SQL
create table branch (branch-name char(15), branch-city
primary key (branch-name), check (assets >= 0))
Delete table : drop table r Delete tuples : Delete from r
Add Attribute : alter table r add AD Drop attribute : alter table r drop A
Data Manipulation Language DML- Data Manipulation Language: Statements used for managing data within schema objects. SELECT - retrieve data from the a database INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table, the space for the records remain CALL - call a PL/SQL or Java subprogram Example Setting Schema & Attributes
street, customer-city)
amount)
number) Basic SQL Query
1. select branch-name from loan
2. select distinct branch-name from loan Remove duplicates 3. select all branch-name from loan Retain Duplicates
1. select loan-number from loan where branch-name =
select customer-name, borrower.loan-number, amount from borrower, loan where borrower.loan-number = loan.loan-number Basic SQL Query
select customer-name, borrower.loan-number
select customer-name, T.loan-number, S.amount from
Tuple variables are most useful for comparing two tuples in the same relation.
select customer-name from customer where customer-
Basic SQL Query
select distinct customer-name from borrower, loan where
order by customer-name select * from loan order by
Set Operations
an account, or both (select customer-name from depositor) union (select customer-name from borrower) Removes Duplicates
both a loan and an account (select distinct customer-name from depositor) intersect (select distinct customer-name from borrower)
account but no loan (select distinct customer-name from depositor) except (select customer-name from borrower)
Aggregate Functions avg, min, max, sum, count Example : “Find the average balance for each customer who lives in Harrison and has at least three accounts.” select depositor.customer-name, avg (balance) from depositor, account, customer where depositor.account-number = account.account-
group by depositor.customer-name having count
(distinct depositor.account-number) >= 3 Nested Subqueries
who are borrowers from the bank and who appear in the list of account holders select distinct customer-name from borrower where
branches that have assets greater than those of at least one branch located in Brooklyn. select branch-name from branch where assets
>some : greater than at least one member Views Find for each branch the sum of the amounts of all the loans at the branch. create view branch-total-loan(branch-name, total-loan) as select branch-name, sum(amount) from loan groupby
Complex Queries
across all branches of the total balance at each branch.” select max(tot-balance) from (select branch-name, sum(balance) from account group by branch-name) as branch-total (branch-name, tot-balance)
maximum balance; if there are many accounts with the same maximum balance, all of them are selected. with max-balance (value) as select max(balance) from account select account-number from account, max-balance where account.balance = max-balance.value Modification of the Database
delete from account where branch-name = ’Perryridge’
insert into account (account-number, branch-name,
accounts whose balance is greater than average” update account set balance = balance * 1.05 where balance > select avg (balance) from account Joined Relations Joined Relations Joined Relations Joined Relations Download 1.42 Mb. Do'stlaringiz bilan baham: 
|