I’m a beginning sql student. My Task is: Return Parking License Type and Number

Question

I’m a beginning sql student. My Task is: Return Parking License Type and Number of Parking Licenses for each License Type. Listed below are my tables and some data. I’m confused about how to get the count showing next to the type of License. The scenario is a university parking system. I'm not sure whether to use count or an outer join. I'm shaky on both! Here’s what I’ve tried.

Select pid, ptype, description

from Permit_Type,

Permit Where Count(*) ??????

Create Table License_Type (

Ltype int NOT NULL, -- type, e.g. Faculty, Student

description varchar (30),

price decimal (4,2) NOT NULL,

otype char (1) NOT NULL, -- 1=student, 2=faculty

PRIMARY KEY (Ltype) );

Ltype Description Price otype

1 Staff Day Parking 5 2

2 Staff Evening 5 2

3 Student and Visitor Day 20 1

4 Student and Visitor Evening 15 1

5 Student 24 hour parking 35 1

6 Staff 24 hour parking 40 2

Create Table License

(Lid int NOT NULL,

oid int NOT NULL,

Ltype int NOT NULL,

expiredate date NOT NULL,

PRIMARY KEY (Lid),

FOREIGN KEY (oid)

REFERENCES Owner (oid),

FOREIGN KEY (Ltype) REFERENCES License_Type (Ltype) );

Lid oid Ltype expiredate

10 2 6 01-JAN-19

11 1 5 02-MAY-19

12 3 5 14-APR-19

13 4 2 07-DEC-18

14 5 1 15-JUL-19

Explanation / Answer

First of all, the sample query to do OUTER JOIN is given below.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

The Query as per your requirement is given below.

SELECT Ltype, count(*) FROM License GROUP BY Ltype;

NOTE: There is no need to do any JOIN operation as both Parking License Type and Number of Parking Licenses for each License Type can be found from LICENSE table.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.