Database Design SQL Write Queries Create the below tables in

Database Design SQL Write Queries

Create the below tables in MySQL Server and then give query inputs and outputs based on questions given:

a) Find the number of students who applied to Cornell university.

b) Find the number of applications to all the universities.

c) Find the number of applications to all the universities where the student should be counted only once in each university. NOTE: students can apply to several majors at the same university. Remove duplicate entries.

Table: College State enrollment cName CA 15000 Stanford CA 36000 Berkeley MA MIT 10000 NY Cornell 21000 Table: Student Name GPA. SizeHS 123 Amy 1000 Bob .6 1500 234 35 Craig 35 500 Doris 29 2000 567 Edward 678 3.8 200 Gary 34 800 789 Helen 37 800 987 876 Irene 39 400 29 1500 765 654 Table: Apply cName decision CS 123 Stanford Stanford EE N 123 Berkeley CS 123 EE 123 Cornell Berkeley Biology IN 234 MIT Bioengineering Y 345 Cornell Bioengineering N CS Cornell EE IN Cornell 678 Stanford History Stanford CS 987 Berkeley CS 987 876 Stanford CS IN 876 MIT Biology 876 MIT Marine Biology N History Y 765 Stanford History N 765 Cornell Psycholog Y 765 Cornell 5413 MIT CCS N

Solution

a) select distinct sid from apply where cname=\'cornell\';

b) select count(*) from apply;

c) select distinct sid from apply;