n addition to logging into MySQL or MariaDB with the mysql client and entering t

Question

n addition to logging into MySQL or MariaDB with the mysql client and entering the SQL statements shown already in this chapter, here are a few exercises to get some more practice playing with the mysql client and to help you better understand the basics. Rather than use generic names like books and book_id, you’re asked to use more realistic names. In that same spirit, use fairly realistic data (e.g., “John Smith” for a person’s name) when entering data in these exercises. Log into MySQL or MariaDB using the mysql client and switch the default database to the database, test. Create two tables called contacts and relation_types. For both tables, use column type INT for number columns and CHAR for character columns. Specify the maximum number of characters you want with CHAR—otherwise MySQL wills set a maximum of one character, which is not very useful. Make sure that you allow for enough characters to fit the data you will enter later. If you want to allow characters between numbers (e.g., hyphens for a telephone number), use CHAR. For the contacts, you will need six columns: name, phone_work, phone_mobile, email, relation_id. For the relation_types table, there should be only two columns: relation_id and relationship. When you’re finished creating both tables, use the DESCRIBE statement to see how they look. Enter data in the two tables created in the previous exercise. Enter data in the second table, relation_types first. Enter three rows of data in it. Use single-digit, sequential numbers for the first column, but the following text for the second column: Family, Friend, Colleague. Now enter data in the table named contacts. Enter at least five fictitious names, telephone numbers, and email addresses. For the last column, relation_id, enter single digits to correspond with the relation_id numbers in the table, relation_types. Make sure you have at least one row for each of the three potential values for relation_id. Execute two SELECT statements to retrieve all of the columns of data from both tables that you created and filled with data from the previous two exercises. Then run a SELECT statement that retrieves only the person’s name and email address from the table named contacts. Change some of the data entered in the previous exercises, using the UPDATE statement. If you don’t remember how to do that, refer back to the examples in this chapter on how to change data in a table. First, change someone’s name or telephone number. Next, change someone’s email address and his or her relationship to you (i.e., relation_id). Do this in one UPDATE statement. Run a SELECT statement that joins both tables created in the first exercise. Use the JOIN clause to do this (the JOIN clause was covered in this chapter, so look back at the example if you don’t remember how to use it). Join the tables on the common column named relation_id—this will go in the WHERE clause. To help you with this, here’s how the clauses for the tables should look: ... FROM contacts JOIN relation_types WHERE contacts.relation_id = relation_types.relation_id ... Select the columns name and phone_mobile, but only for contacts who are marked as a Friend—you’ll have to add this to the WHERE with AND. Try doing this based on the value of relation_id and then again based on the value of the relationship column (Dyer)

n addition to logging into MySQL or MariaDB with the mysql client and entering the SQL statements shown already in this chapter, here are a few exercises to get some more practice playing with the mysql client and to help you better understand the basics. Rather than use generic names like books and book_id, you’re asked to use more realistic names. In that same spirit, use fairly realistic data (e.g., “John Smith” for a person’s name) when entering data in these exercises. Log into MySQL or MariaDB using the mysql client and switch the default database to the database, test. Create two tables called contacts and relation_types. For both tables, use column type INT for number columns and CHAR for character columns. Specify the maximum number of characters you want with CHAR—otherwise MySQL wills set a maximum of one character, which is not very useful. Make sure that you allow for enough characters to fit the data you will enter later. If you want to allow characters between numbers (e.g., hyphens for a telephone number), use CHAR. For the contacts, you will need six columns: name, phone_work, phone_mobile, email, relation_id. For the relation_types table, there should be only two columns: relation_id and relationship. When you’re finished creating both tables, use the DESCRIBE statement to see how they look. Enter data in the two tables created in the previous exercise. Enter data in the second table, relation_types first. Enter three rows of data in it. Use single-digit, sequential numbers for the first column, but the following text for the second column: Family, Friend, Colleague. Now enter data in the table named contacts. Enter at least five fictitious names, telephone numbers, and email addresses. For the last column, relation_id, enter single digits to correspond with the relation_id numbers in the table, relation_types. Make sure you have at least one row for each of the three potential values for relation_id. Execute two SELECT statements to retrieve all of the columns of data from both tables that you created and filled with data from the previous two exercises. Then run a SELECT statement that retrieves only the person’s name and email address from the table named contacts. Change some of the data entered in the previous exercises, using the UPDATE statement. If you don’t remember how to do that, refer back to the examples in this chapter on how to change data in a table. First, change someone’s name or telephone number. Next, change someone’s email address and his or her relationship to you (i.e., relation_id). Do this in one UPDATE statement. Run a SELECT statement that joins both tables created in the first exercise. Use the JOIN clause to do this (the JOIN clause was covered in this chapter, so look back at the example if you don’t remember how to use it). Join the tables on the common column named relation_id—this will go in the WHERE clause. To help you with this, here’s how the clauses for the tables should look: ... FROM contacts JOIN relation_types WHERE contacts.relation_id = relation_types.relation_id ... Select the columns name and phone_mobile, but only for contacts who are marked as a Friend—you’ll have to add this to the WHERE with AND. Try doing this based on the value of relation_id and then again based on the value of the relationship column (Dyer)

n addition to logging into MySQL or MariaDB with the mysql client and entering the SQL statements shown already in this chapter, here are a few exercises to get some more practice playing with the mysql client and to help you better understand the basics. Rather than use generic names like books and book_id, you’re asked to use more realistic names. In that same spirit, use fairly realistic data (e.g., “John Smith” for a person’s name) when entering data in these exercises. Log into MySQL or MariaDB using the mysql client and switch the default database to the database, test. Create two tables called contacts and relation_types. For both tables, use column type INT for number columns and CHAR for character columns. Specify the maximum number of characters you want with CHAR—otherwise MySQL wills set a maximum of one character, which is not very useful. Make sure that you allow for enough characters to fit the data you will enter later. If you want to allow characters between numbers (e.g., hyphens for a telephone number), use CHAR. For the contacts, you will need six columns: name, phone_work, phone_mobile, email, relation_id. For the relation_types table, there should be only two columns: relation_id and relationship. When you’re finished creating both tables, use the DESCRIBE statement to see how they look. Enter data in the two tables created in the previous exercise. Enter data in the second table, relation_types first. Enter three rows of data in it. Use single-digit, sequential numbers for the first column, but the following text for the second column: Family, Friend, Colleague. Now enter data in the table named contacts. Enter at least five fictitious names, telephone numbers, and email addresses. For the last column, relation_id, enter single digits to correspond with the relation_id numbers in the table, relation_types. Make sure you have at least one row for each of the three potential values for relation_id. Execute two SELECT statements to retrieve all of the columns of data from both tables that you created and filled with data from the previous two exercises. Then run a SELECT statement that retrieves only the person’s name and email address from the table named contacts. Change some of the data entered in the previous exercises, using the UPDATE statement. If you don’t remember how to do that, refer back to the examples in this chapter on how to change data in a table. First, change someone’s name or telephone number. Next, change someone’s email address and his or her relationship to you (i.e., relation_id). Do this in one UPDATE statement. Run a SELECT statement that joins both tables created in the first exercise. Use the JOIN clause to do this (the JOIN clause was covered in this chapter, so look back at the example if you don’t remember how to use it). Join the tables on the common column named relation_id—this will go in the WHERE clause. To help you with this, here’s how the clauses for the tables should look: ... FROM contacts JOIN relation_types WHERE contacts.relation_id = relation_types.relation_id ... Select the columns name and phone_mobile, but only for contacts who are marked as a Friend—you’ll have to add this to the WHERE with AND. Try doing this based on the value of relation_id and then again based on the value of the relationship column (Dyer)

Explanation / Answer

// Creating the table relation with two columns. relation_id is set as primary key.

CREATE TABLE relation

(

relation_id INT,

relationship CHAR(25),

PRIMARY KEY (relation_id)

);

// Creating table contacts. name is declared as primary key and relation_id as foreign key.

CREATE TABLE contacts

(

name CHAR(25),

phone_work CHAR(14),

phone_mobile CHAR(14),

email CHAR(30),

relation_id INT,

PRIMARY KEY (name),

FOREIGN KEY (relation_id) REFERENCES relation (relation_id)

);

// Describing both the tables relation and contacts.

DESC relation;

DESC contacts;

// Inserting data to relation table.

INSERT INTO relation VALUES (1, 'Family');

INSERT INTO relation VALUES (2, 'Friend');

INSERT INTO relation VALUES (3, 'Colleague');

// Insertind records to contacts table.

INSERT INTO contacts VALUES ('Thomas', '99-99-9999', '88-88-8888', 'my@india.com', 1);

INSERT INTO contacts VALUES ('John', '59-99-9999', '88-55-8888', 'you@asean.com', 2);

// Query- selecting name and phone_mobile from contacts table for contacts whose relation is marked as Friend.

// First by using relation id.

SELECT name, phone_mobile

FROM contacts

WHERE relation_id = 2;

// By using relationship name.

SELECT name, phone_mobile

FROM relation As r

INNER JOIN contacts AS c

ON c.relation_id = r.relation_id

WHERE relationship = "Friend" ;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.