Mestrado Online em Informação e Sistemas Empresariais 2019/2020

Introdução às Bases de Dados 2º Semestre

Lab 1b: Intrdução ao SQL

No Lab 1, criou uma base de dados simples do banco ilustrada na figura:

Figure 1. Example database

Vamos supor que já criou a base de dados, e vamos mostrar commo lhe aceder com o
commando mysql.

Vamos mostrar primeiro alguns comandos básicos em SQL.

Parte I: experiências básicas

1. Na linha de comando do seu computador, execute o comando de login antes

explicado:
mysql -h db.tecnico.ulisboa.pt -u ist1xxxxx -p

IST/DEI Page 1 of 4
 SIBD

2. Ligue-se à sua base de dados com o comando:

USE db_xxxxx;
Substitua, xxxxx pelo seu numero de utilizador (matrícula)

3. Introduza o comando:
SHOW TABLES;
Verá na saída a lista das tabelas disponíveis para si no SGBD que carregou
anteriormente.

4. Obtain information about the structure of the account table:

DESCRIBE account;
SHOW CREATE TABLE account;
The meaning of the columns should be clear to you, but what about the primary key
and foreign key? Find the primary key and foreign key.

5. At the mysql> prompt, execute the following query and inspect the contents of table
account:
SELECT * FROM account;

6. Now execute the following query and inspect the contents of table depositor:
SELECT * FROM depositor;

7. Run the following query and explain, in your own words, what this query is doing:
SELECT * FROM account, depositor;

8. Now explain what this query is doing:

SELECT *
FROM account, depositor
WHERE account.account_number = depositor.account_number;

9. What are the differences between the result of the previous query and this one:
SELECT * FROM account NATURAL JOIN depositor;

10. Try joining 3 tables:

SELECT * FROM account, depositor, customer;

11. If table account has 7 rows, table depositor has 7 rows, and table customer has 13
rows, can you predict how many rows will appear in the result of the previous query?

12. Now run a similar query, but with some additional criteria:
SELECT *

IST/DEI Page 2 of 4
 SIBD

FROM account, depositor, customer

WHERE account.account_number = depositor.account_number
AND depositor.customer_name = customer.customer_name;
What information is this query showing?

13. What are the differences between the result of the previous query and this one:
SELECT *
FROM account NATURAL JOIN depositor NATURAL JOIN customer;

14. This query works:

SELECT account_number
FROM account NATURAL JOIN depositor NATURAL JOIN customer;
But this query does not work:
SELECT account_number
FROM account, depositor, customer
WHERE account.account_number = depositor.account_number
AND depositor.customer_name = customer.customer_name;
How can you fix the second query to make it work?

15. In the previous queries, you used mostly SELECT * to show all columns. However, in
practice you should select only those columns which are relevant to answer the given
question. For example, if the question is “Which accounts have a balance of 700?”
then the query is:
SELECT account_number FROM account WHERE balance = 700;
As another example, if the question is “Which customers live in Brooklyn?” then the
query should be:
SELECT customer_name FROM customer WHERE customer_city = 'Brooklyn';
Note that, in both cases, the column being selected is the primary key of the
corresponding table. The question may also ask for a specific column (e.g. balance of
table account) which is not the primary key of a table.

Part II: Queries às base de dados.

Write a single SQL query to answer each of the following questions:

In this lab, you should avoid using natural joins. Instead, you should indicate explicitly
(i.e. in the WHERE clause) which joining criteria are being used to join the tables.

1. Some customers have accounts with balance over 500€. Find their names (but, for
privacy reasons, do not show their balances). There should be no repeated names in
the result.

IST/DEI Page 3 of 4
 SIBD

2. Some customers have loans with amounts between 1000€ and 2000€. We need to find
the cities where those customers live.
3. What would be the new balance of the accounts in Perryridge, if this branch decided
to charge a commission of 1% to each account? (You do not need to update the table,
just show what the new balance of each account would be)
4. What is the account number and balance for each account of the customer associated
with loan L-15?
5. Who are the customers who live in cities where the bank has branches? (Do not show
duplicates in the result)
6. What are the assets of the branch where customer ‘Jones’ has an account?
7. In this question we are interested only in those customers with a name beginning with
‘J’ and ending with ‘s’. In which branches do these customers have an account?
8. In this question we are interested only in those customers who live in a street with 4
letters. If these customers have a loan, what is the amount of their loans? (In the
results, show the customer name, the customer street, the loan numbers, and the
amounts of their loans)
9. Who is the customer who has both an account and a loan in the same branch?

IST/DEI Page 4 of 4