I need help on my SQL lab.

User Generated

nyibu

Computer Science

Description

I need help on my SQL lab.

lab_1.docx

Unformatted Attachment Preview

SQL Database Lab Page 1 Lab 1 This lab introduces some common SQL commands with examples. Required Software and database 1. mysql 2. HeidiSql: GUI-based, Visual query tool for MySQL. 2. World database (to be created from a script). The World Database World is a sample database provided by MySQL. The data is from the 1990s, but it's still useful. There are three tables in the database. It's not a great database design: the Continent attribute is an enumeration of Strings, the Region is a String, and Language is a String. The database is created using the SQL script provided (WorldDbCreation.sql). Format of Exercises Write the SQL command you use to get the data. If the result is requested, write that, too. 1 What Tables are in the Database? Connect to the world database. What tables are in the database? Answer: 2 What columns are in the City table? There are two commands to show the columns in a table. Show both: [The is a list of data types in the last page] SQL Database Lab Page 2 Table: City Complete the table. For INT(n) just write integer, for FLOAT(n) just write float. Field Name Type Can be Null? Key? Default Type Can be Null? Key? Default char(2) NO Key? Default Table: Country Field Name Code2 Table: CountryLanguage Field Name CountryCode Language IsOfficial Percentage Type Can be Null? 2.1 Are SQL commands case sensitive? Answer: NO 2.2 In MySQL are names case sensitive? Write the answer that you experimentally observe. Database names? NO Table names? NO Field (column) names? NO Note: The answer for Database and Table names depends on the operating system and configuration of MySQL. It may be case sensitive or not. Write what you observe using our server SQL Database Lab Page 3 3 Primary Key and Foreign Key In this database, the primary key in one table is used as a foreign key in the other two tables. What is this key’s name as a primary and as a foreign key? (HINT: This database uses one name for the primary key and another for the foreign keys which is typical. If you examine the columns in all three tables, you should have no problem figuring out which column links the uerying Data The SELECT command returns data from tables. SELECT * FROM tablename SELECT * FROM tablename LIMIT n (only return n results) SELECT field1, field2, field3 FROM tablename WHERE condition [ LIMIT n ] SELECT fields FROM tablename WHERE condition ORDER BY field [ASC|DESC] 3.1 What are the first 3 Cities in Database? Answer: 3.2 What are the 3 most populous countries in the World? SELECT ... ORDER BY fieldname ASC (ascending) SELECT ... ORDER BY fieldname DESC (descending - largest value first)) What are the 3 most populous countries in the world? Write their name and population. sql> SELECT Name FROM country ORDER BY population DESC limit 3; Answer: What is the smallest country in the world? Answer: (name and size) 3.3 Select with WHERE SELECT field1, field2, field3 FROM table WHERE condition Example: SELECT * FROM Country WHERE Name='Japan' What is the CountryCode for Thailand? Answer: Answer: (at least 3 cities) 3.4 SELECT using WHERE, ORDER, and LIMIT SELECT fields FROM table WHERE condition ORDER BY field [ASC|DESC] LIMIT n What Country has the largest population in Europe? What is its population? Answer: SQL Database Lab Page 4 What is the country code used for Puerto Rico in this database? Answer: PRI What is(are) the language spoken in Puerto Rico as shown in this database? Answer: According to this database what is the capital of Puerto Rico? (HINT: You may want to use two commands to get the answer to this question)Which City(s) in Puerto Rico have a population of more than 200,000 but less than 400,000? Answer: 3.5 UPDATE Update the names of presidents of the East African Countries. 4 ENTITY RELATION DIAGRAM Draw the ERD of the database (only one diagram like the one we drew under normalization). Show the cardinality of the relationships. SQL Database Lab Page 5 SQL Data Types SQL has many data types, and some are not recognized by all databases. For example, every SQL datatype recognizes INTEGER and SMALLINT, but may not allow INT(11). Data Type INTEGER BIGINT SMALLINT TINYINT INT(n) BOOLEAN DATE DATETIME CHAR(m) BLOB BLOB(length) ENUM('apple','grape'...) Description Data Type Description
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer


Anonymous
Nice! Really impressed with the quality.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags