1 Student Advisor

A university has started a student-advisor plan which assigns a professor as an advisor to each student for academic guidance. Write a query to find the roll number and names of students for academic guidance. Write a query to find the roll number and name of students who either have a male advisor with a salary more than 15.000 r a female advisor with a salary of more than 20.000

There are two tables in the database: student_information and faculty_information. The primary key of student_information whereas that of faculty_information is employee_ID

Schema

You are provided 2 tables: student_information, faculty_information

Note: information about any professor who acts as an advisor to a student is always present in faculty_information.

Sample Data Tables

Sample Outout

2 Claire

Explanation

  • Student with roll number 2 is advised by a male teacher with a salary of 21.000 which is more than 15.000 and hence is displayed in the output.
  • Students 1 and 3 are advised by a female teacher whose salary is less than 20.000 and hence are not present in the output
SELECT *
FROM student_information as si
         JOIN faculty_information as fi on si.advisor = fi.employee_id
WHERE 1 = 1
    AND (
                  fi.gender = "M"
              AND fi.salary > 1 5000
          )
   OR (
            fi.gender = "F"
        AND fi.salary > 20000
    )

2. Profitable Stocks

A stock is considered profitable if the predicted price is strictly greater than the current price. Write a query to find the stock_codes of all the stocks which are profitable based on this definition. Sort the output in ascending order.

There are two tables in the database: price_today and price_tomorrow. Their primary keys are stock_code

Schema

There are 2 tables: price_today, price_tomorrow

Note: Both tables contain all stocks listed on the stock exchange

Sample Output

GOOGL
TITAN

Explanation

  • For TITAN, today’s stock price is 560 and its predicted price is 750, hence it is a profitable stock
  • For MRF, today’s stock price is 950 and its predicted price is 800, hence it is not a profitable stock.
SELECT *
FROM price_today as t1
         JOIN price_tomorrow as t2
              ON t1.stock_code = t2.stock_code
--                   AND t2.price > t1.price
WHERE 1 = t
  AND t2.price > t1.price