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 who either have a male advisor with a salary of more than 15,000 or 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 is roll_number whereas that of
faculty_information is employee_ID.
SELECT roll_number,name
FROM student_information a
INNER JOIN faculty_information b
ON a.advisor = b.employee_ID
WHERE (b.gender = 'M' and b.salary>15000) or (b.gender = 'F' and b.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.
SELECT a.stock_code
FROM price_today a
INNER JOIN price_tomorrow b
ON a.stock_code = b.stock_code
WHERE b.price>a.price
ORDER BY stock_code asc;Student-analysis
SELECT a.roll_number,a.name
FROM student_information a
INNER JOIN examination_marks b
ON a.roll_number = b.roll_number
GROUP BY b.roll_number
HAVING SUM(b.subject_one + b.subject_two + b.subject_three) < 100;Merit-rewards
SELECT ei.employee_ID, ei.name
FROM employee_information ei
JOIN last_quarter_bonus b ON b.employee_ID = ei.employee_ID
WHERE ei.division LIKE 'HR'
AND b.bonus >= 5000;Country-codes
SELECT a.customer_id,a.name,concat("+",b.country_code,a.phone_number)
FROM customers as a
LEFT join country_codes as b
ON a.country=b.country
ORDER BY a.customer_id;The Above Solution Help you to get the Certificate in SQL Basic