OFFICE table & RESIDENTS table

Question

Answer the following SQL Queries.

The data is split into several tables, shown below:

The OFFICE table shows the office number, office name, address, area, city, state, and ZIP code.
OFFICE table

Stay Well stores information about the owners of each property in the OWNER table. Each owner is identified by a unique owner number that consists of two uppercase letters.  followed by a three-digit number. For each owner, the table also includes the last name, first name, address, city, state, and ZIP code. And Notice the owners are from across the United States. So Although some apartments may be owned by a couple or a family, only the primary contact is given.
OWNER table

Each property at each location is identified by a property ID.  as seen in the PROPERTY table. Each property also includes the office number that manages the property, address, floor size, the number of bedrooms. And The number of floors, monthly rent per property. And the owner number. So The PROPERTY_ID is an integer unique for each property.
PROPERTY table

Also for the following:

The RESIDENTS table includes details about the residents living in each property. So The RESIDENTS table includes the first name and surname (last name) for each of the residents. And Along with a resident ID. The PROPERTY_ID is the unique identification number of the property in which they are staying.
RESIDENTS table

The SERVICE_REQUEST table shows requests that residents have put into the offices for maintenance. Each row contains a unique service ID number, the property ID. So the category number associated with the type of work. And The office managing the property, a description of the request, the current status of the request. So The estimated hours to complete the request, the hours spent on the request, and the scheduled service date.
SERVICE_REQUEST table

The SERVICE_CATEGORY table includes details of these services. And The CATEGORY_NUM provides a unique number for the service. And CATEGORY_DESCRIPTION stores a description of what the service is.
SERVICE_CATEGORY table

1. 1. 1.  Revoke all privileges from user Adams.
      2.  Delete the OWNER_INDEX3 index from the OWNER table.
      3. List every table that contains a column named OWNER_NUM. The output table should display one column named TABLE_NAME.

Explanation

SQL Queries stand for a structured query language. And This language is used to create and maintain the data into the database. So SQL provides us the different commands like create, print, update and delete data.

Some of the commands for SQL Queries are as follows:

• • SELECT– This command is used to select the data from a table.
  • CREATE– This command is used to create tables and databases etc.
  • INSERT– This command is used to insert the values into the database.
  • UPDATE– this command tis used to update any value in the table
  • And also in addition to this, there are many other commands are available in the SQL. Such as MAX(), MIN(), SUM(), etc to compute the maximum, minimum, and sum of the attribute passed as the parameter in the method.

Required SQL Queries are:

• • Revoke all privileges from user Adams.

Select * from SERVICE_CATEGORY
where SERVICE_CATEGORY.CATEGORY_NUM=SERVICE_REQUEST.CATEGORY_NUM
and RESVICE_REQUEST.PropertyID = RESIDENTS.PropertyID
and RESIDENTS.FName="Adams";

• • Delete the OWNER_INDEX3 index from the OWNER table.

ALTER TABLE OWNER
DROP INDEX OWNER_INDEX3;

• • List every table that contains a column named OWNER_NUM. And The output table should display one column named TABLE_NAME.
    So Here the schema is the schema of our own database.

SELECT DISTINCT TABLE_NAME from
schema.column where
COLUMN_NAME="OWNER_NUM";

Also read, Display a proof for the following theorems: