Creating databases and tables in Hadoop - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a new database named 'sales_db'.

Hadoop

CREATE DATABASE [1];

Drag options to blanks, or click blank then click option'

ADATABASE

BCREATE

CTABLE

Dsales_db

Attempts:

3 left

💡 Hint

Common Mistakes

Using keywords like CREATE or TABLE instead of the database name.

Forgetting to specify the database name.

The command CREATE DATABASE sales_db; creates a new database named 'sales_db'.

2fill in blank

medium

Complete the code to create a table named 'customers' with columns 'id' (int) and 'name' (string).

Hadoop

CREATE TABLE [1] (id INT, name STRING);

Drag options to blanks, or click blank then click option'

Acustomers

Bsales_db

Corders

Dproducts

Attempts:

3 left

💡 Hint

Common Mistakes

Using the database name instead of the table name.

Using unrelated table names.

The table name 'customers' is used in the CREATE TABLE statement to define the new table.

3fill in blank

hard

Fix the error in the code to create a database named 'analytics'.

Hadoop

CREATE [1] analytics;

Drag options to blanks, or click blank then click option'

ATABLE

BDATABASE

CSCHEMA

DINDEX

Attempts:

3 left

💡 Hint

Common Mistakes

Using TABLE or INDEX instead of DATABASE.

Confusing SCHEMA with DATABASE in Hadoop.

The correct keyword to create a database is DATABASE. Using CREATE DATABASE analytics; creates the database.

4fill in blank

hard

Fill both blanks to create a table named 'employees' with columns 'emp_id' (int) and 'emp_name' (string) in the database 'company_db'.

Hadoop

USE [1];
CREATE TABLE [2] (emp_id INT, emp_name STRING);

Drag options to blanks, or click blank then click option'

Acompany_db

Bemployees

Csales_db

Dcustomers

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong database name in USE statement.

Mixing up table names.

First, switch to the database company_db using USE company_db;. Then create the table employees with the specified columns.

5fill in blank

hard

Fill all three blanks to create a partitioned table named 'logs' with columns 'log_id' (int), 'log_message' (string), partitioned by 'log_date' (string).

Hadoop

CREATE TABLE [1] (log_id INT, log_message STRING) PARTITIONED BY ([2] STRING) STORED AS [3];

Drag options to blanks, or click blank then click option'

Alogs

Blog_date

CTEXTFILE

Dlog_message

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-partition column in the PARTITIONED BY clause.

Forgetting to specify storage format.

Mixing up column names.

The table name is logs. It is partitioned by the column log_date of type STRING. The storage format is TEXTFILE.

Practice

1. What is the main purpose of creating a database in Hadoop?

easy

A. To run queries faster

B. To store data in rows and columns

C. To organize data into separate groups

D. To delete old data automatically

Solution

1. Step 1: Understand the role of a database

   A database is used to organize data logically into separate groups or collections.

2. Step 2: Differentiate database from tables

   Tables store data in rows and columns inside a database, but the database itself is for grouping.

3. Final Answer:

   To organize data into separate groups -> Option C

4. Quick Check:

   Database = Organize data groups [OK]

Hint: Databases group data; tables hold the data [OK]

Common Mistakes:

• Confusing database with table
• Thinking database stores data directly
• Assuming database speeds up queries automatically

2. Which of the following is the correct syntax to create a database named sales_db in Hive?

easy

A. CREATE DATABASE sales_db;

B. MAKE DATABASE sales_db;

C. CREATE DB sales_db;

D. NEW DATABASE sales_db;

Solution

1. Step 1: Recall Hive syntax for database creation

   The correct command to create a database in Hive is CREATE DATABASE database_name;.

2. Step 2: Check each option

   Only CREATE DATABASE sales_db; matches the correct syntax exactly.

3. Final Answer:

   CREATE DATABASE sales_db; -> Option A

4. Quick Check:

   CREATE DATABASE = correct syntax [OK]

Hint: Use CREATE DATABASE, not MAKE or NEW [OK]

Common Mistakes:

• Using MAKE or NEW instead of CREATE
• Abbreviating DATABASE as DB
• Missing semicolon at end

3. What will be the result of running this Hive query?

CREATE TABLE employees (id INT, name STRING, salary FLOAT);

medium

A. Creates a table named employees with three columns

B. Throws a syntax error due to missing database name

C. Creates a database named employees

D. Creates a table with no columns

Solution

1. Step 1: Analyze the CREATE TABLE statement

   The query uses correct syntax to create a table named employees with columns id, name, and salary.

2. Step 2: Check for errors or missing parts

   Database name is optional if default database is used; no syntax error here.

3. Final Answer:

   Creates a table named employees with three columns -> Option A

4. Quick Check:

   CREATE TABLE with columns = creates table [OK]

Hint: CREATE TABLE defines columns inside parentheses [OK]

Common Mistakes:

• Thinking database name is mandatory
• Confusing table and database creation
• Assuming syntax error without checking carefully

4. Identify the error in this Hive query:

CREATE TABLE users id INT, name STRING;

medium

A. Table name is invalid

B. Missing parentheses around column definitions

C. Data types are incorrect

D. CREATE TABLE keyword is misspelled

Solution

1. Step 1: Review the syntax for CREATE TABLE

   Column definitions must be enclosed in parentheses after the table name.

2. Step 2: Check the given query

   The query misses parentheses around id INT, name STRING, causing syntax error.

3. Final Answer:

   Missing parentheses around column definitions -> Option B

4. Quick Check:

   Columns need parentheses [OK]

Hint: Always use parentheses for columns in CREATE TABLE [OK]

Common Mistakes:

• Forgetting parentheses
• Assuming table name is wrong
• Thinking data types are invalid

5. You want to create a database company_db and inside it a table departments with columns dept_id INT and dept_name STRING. Which sequence of commands is correct?

hard

A. CREATE TABLE company_db.departments (dept_id INT, dept_name STRING);

B. CREATE DATABASE company_db; CREATE TABLE departments dept_id INT, dept_name STRING;

C. USE company_db; CREATE DATABASE company_db; CREATE TABLE departments (dept_id INT, dept_name STRING);

D. CREATE DATABASE company_db; USE company_db; CREATE TABLE departments (dept_id INT, dept_name STRING);

Solution

1. Step 1: Create the database first

   Use CREATE DATABASE company_db; to make the database.

2. Step 2: Switch to the new database

   Use USE company_db; to set the context for table creation.

3. Step 3: Create the table with correct syntax

   Use CREATE TABLE departments (dept_id INT, dept_name STRING); inside the database.

4. Final Answer:

   CREATE DATABASE company_db; USE company_db; CREATE TABLE departments (dept_id INT, dept_name STRING); -> Option D

5. Quick Check:

   Create DB, switch DB, then create table [OK]

Hint: Create DB, then USE it before creating tables [OK]

Common Mistakes:

• Trying to create table before database
• Wrong order of USE and CREATE DATABASE
• Missing parentheses in CREATE TABLE