文章目录
- 1. Table and Data
- 2. Table Constraints
- 3. Altering Table
- 4. SQL Select Ⅰ
- 5. SQL Select II
- Lab1
- lab2
- Task 1:
- 1. Print the Names of all Teachers.
- 2. Retrieve all students and all of the information held about them (excluding enrolment).
- 3. Retrieve the student ID’s who study MC2.
- 4. Retrieve the list of student ID’s, names and email address for students who study HCI-101.
- 5. Retrieve the Names of students who do not take the module ‘MC1’
- 6. Retrieve the Names and Emails of students who study both ‘MC1’ and ‘MC2’.
- 7. Retrieve the Names and Telephone Numbers of lecturers whose office is in the ‘BES’ building.
- Task 2:
- 8. Retrieve the IDs of students who take the module 'MC1' OR 'MC2'.
- 9. Retrieve the Names and Emails of students who study either in 'MC1' OR 'MC2'.
- 10. Retrieve the Names and Emails of students whose email is not ended with '@gmail.com'.
- 11. Identify if there is any student who enrolled in HSB, HCI-101 and MC2 and Retrieve the Names and ID of them.
- 12. Retrieve the ID and Names of students whose name includes 'ia'
- 13. Retrieve the Names and Emails of students who study not in 'MC1' OR 'MC2' OR 'HCI-101'.
- lab3
- Data
- Task 1:
- Task 2:
- a. Find the lowest price of any CD. The result should be presented in a column named ‘Cheapest CD
- b. Identify the difference between the most expensive and cheapest CD.
- c. Find the number of CDs costing 9.99. The result should be presented in a column named ‘Count of £9.99 CD’s’
- d. Find the title of the most expensive Electronica CD(s).
- f. List all the information about the cheapest (lowest priced) CDs.
- Task 3:
这里通过
XAMPP
来用
PhpMyAdmin
创建数据库
1. Table and Data
1.1 Creating a Database
- First, we need to create a schema
CREATE SCHEMA name;
CREATE DATABASE name;
- If you want to create tables in this schema, you need to tell MySQL to “enter” into this schema, type:
Use name;
1.2 Creating Tables
- Syntax
CREATE TABLE [IF NOT EXISTS] name (
col-name datatype [col-options],
:
col-name datatype [col-options],
[constraint-1],
:
[constraint-n]
);
-- Contents between [] are optional
###1.3 Data Types
- For floating-point and fixed-point data types, M is the total number of digits that can be stored.
Data Type | 属性 |
---|---|
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] | 2 Bytes, signed range: -32768 to 32767 |
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] | 3 Bytes |
INT[(M)] [UNSIGNED] [ZEROFILL] | 4 Bytes |
BIGINT[(M)] [UNSIGNED] [ZEROFILL] | 8 Bytes |
DECIMAL / DEC / NUMERIC / FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] | M大小 1~65 |
FLOAT§ [UNSIGNED] [ZEROFILL] | automatically choose single precision or double precision based on the value of p |
CHAR[(M)] | 1 Byte, M is the length, 0 ~ 255 |
VARCHAR(M) | A variable-length string, M is 0 to 65,535 |
- Things between [] are optional
- Date and Time
数据类型 | 描述 |
---|---|
DATE | YYYY-MM-DD |
DATETIME[(fsp)] | ‘YYYY-MM-DD hh:mm:ss[.fraction]’ |
TIMESTAMP | UTC time |
###1.4 Column Options
col-name datatype [col-options]
NOT NULL
- values of this column cannot be null.
UNIQUE
- each value must be unique (candidate key on a single attribute)
DEFAULT value
AUTO_INCREMENT = baseValue
- Must be applied to a key column (primary key, unique key)
- a value (usually max(col) + 1) is automatically inserted when data is added.
- You can also manually provide values to override this behaviour.
ALTER TABLE Persons AUTO_INCREMENT = 100;
1.5 Tuple Manipulation
1.5.1 INSERT
INSERT INTO tablename (col1, col2, …)
VALUES (val1, val2, …),
:
(val1,val2,val3);
- If you are adding a value to every column, you don’t have to list them
INSERT INTO tablename VALUES (val1, val2, …);
1.5.2 UPDATE
UPDATE table-name
SET col1 = val1 [,col2 = val2…]
[WHERE condition]
1.5.3 DELETE
DELETE FROM
table-name
[WHERE condition]
- • If no condition is given then ALL rows are deleted.
2. Table Constraints
CREATE TABLE name (
col-name datatype [col-options],
:
col-name datatype [col-options],
[constraint-1],
:
[constraint-n]
);
2.1 Syntax of Constraints
CONSTRAINT name TYPE details;
- Constraint name is created so that later this constraint can be removed by referring to its name.
- If you don’t provide a name, one will be generated.
- MySQL provides following constraint types
PRIMARY KEY
UNIQUE
FOREIGN KEY
INDEX
2.2 Domain Constraints
- A domain constraint can be defined along with the column or separately:
CREATE TABLE People (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
sex CHAR NOT NULL CHECK (sex IN ('M','F')),
CONSTRAINT id_positive CHECK (id > 0)
);
2.2.1 UNIQUE
CONSTRAINT name UNIQUE (col1, col2, …)
2.2.2 Primary Key
CONSTRAINT name PRIMARY KEY (col1, col2 …)
2.2.3 Foreign Key
CONSTRAINT name
FOREIGN KEY
(col1, col2, ...)
REFERENCES
table-name
(col1, col2, ...)
[ON UPDATE ref_opt
ON DELETE ref_opt]
-- ref_opt: RESTRICT | CASCADE | SET NULL | SET DEFAULT
- The Binary Keyword
- The BINARY keyword instructs MySQL to compare the characters in the string using their underlying ASCII values rather than just their letters.
CREATE TABLE `branch` (
`branchNo` char(4) BINARY NOT NULL,
PRIMARY KEY (`branchNo`),
...);
2.3 Reference Options
-
RESTRICT
– stop the user from doing it- The default option
-
CASCADE
– let the changes flow on -
SET NULL
– make referencing values null -
SET DEFAULT
– make referencing values the default for their column -
These options can be applied to one or both kinds of the table updates:
- ON DELETE
- ON UPDATE
CONSTRAINT `FK_staff_branchNo`
FOREIGN KEY (`branchNo`)
REFERENCES `branch` (`branchNo`)
ON DELETE SET NULL
ON UPDATE CASCADE
3. Altering Table
3.1 Add column
ALTER TABLE table_name
ADD column_name datatype [options like UNIQUE …];
3.2 Drop colum
ALTER TABLE table_name DROP COLUMN column_name;
3.3 Modify column name and definition
ALTER TABLE table_name
CHANGE COLUMN
col_name new_col_name datatype [col_options];
3.4 Modify column definition only
ALTER TABLE table_name
MODIFY COLUMN
column_name datatype [col_options];
###3.5 Adding Constraints
ALTER TABLE table-name
ADD CONSTRAINT name definition;
3.6 Removing Constraints
ALTER TABLE table-name
DROP INDEX name | DROP FOREIGN KEY name | DROP PRIMARY KEY
-- INDEX - Can be used to drop unique keys
3.7 Deleting Tables
DROP TABLE [IF EXISTS] table-name1, table-name2…;
4. SQL Select Ⅰ
SELECT [DISTINCT | ALL]
column-list FROM table-names
[WHERE condition]
[ORDER BY column-list]
[GROUP BY column-list]
[HAVING condition]
4.1 SELECT
SELECT col1[,col2…] FROM table-name;
4.2 DISTINCT and ALL
- Using DISTINCT after the SELECT keyword removes duplicates
4.3 Expressions in SELECT
select a, b, a+b as sum
from dup_test;
4.4 Where
SELECT * FROM table-name
WHERE predicate;
- Asterisk (*) means getting all columns of that table.
4.5 Word Search
4.5.1 LIKE
- We can use the
LIKE
keyword to perform string comparisons in queries
SELECT * FROM books
WHERE bookName LIKE '%crypt%'
;
-
Like is not the same as ‘=’ because it allows wildcard characters
-
It is NOT normally case sensitive
-
The
%
character can represent any number of characters, including none -
The
_
character represents exactly one character
4.5.2 Dealing with Date and Time
- like numbers
SELECT * FROM table-name
WHERE date-of-event < '2012-01-01';
- like a string
SELECT * FROM table-name
WHERE date-of-event LIKE '2014-11-%';
4.6 Select and Cartesian Product
SELECT * FROM Table1, Table2;
- If the tables have columns with the same name
TableName.ColumnName
4.7 Aliases
- Column alias
SELECT column [AS] new-col-name
- Table alias
SELECT * FROM table [AS] new-table-name
Note: You cannot use a column alias in a WHERE clause
- Aliases and ‘Self-Joins’
SELECT A.Name FROM
Employee A,
Employee B
WHERE
A.Dept = B.Dept
AND
B.Name = 'Andy';
4.8 Subqueries
SELECT col1 FROM tablename
WHERE col2 = (
SELECT col FROM tablename
WHERE condition)
- The first FROM part is evaluated first
4.8.1 IN
SELECT columns FROM tables
WHERE col IN set;
SELECT columns FROM tables
WHERE col NOT IN set;
4.8.2 EXISITS
- Using EXISTS we can see whether there is at least one element in a given set.
SELECT columns
FROM tables
WHERE EXISTS set;
- NOT EXISTS is true if the set is empty
SELECT columns
FROM tables
WHERE NOT EXISTS set;
- The set is always given by a subquery
4.8.3 ANY and ALL
- ANY and ALL compare a single value to a set of values
- They are used with comparison operators like = , >, <, <>, >=, <=
val = ANY (set)
- is true if there is at least one member of the set equal to value
val = ALL (set)
- is true if all members of the set are equal to the value
5. SQL Select II
5.1 Joins
SELECT * FROM A CROSSJOINB;
-- same as
SELECT * FROM A,B;
5.2 INNER JOIN
SELECT * FROM A INNERJOINBON condition
- Can also use a USING clause that will output rows with equal values in the specified columns
SELECT * FROM A INNERJOINBUSING (col1, col2)
- col1 and col2 must appear in both A andB
5.3 NATURAL JOIN
SELECT * FROM A NATURALJOINB;
- A NATURAL JOIN is effectively a special caseof anINNERJOIN where the USING clause has specifiedall identicallynamed columns.
5.4 OUTER JOIN
SELECT cols FROM
table1 type OUTER JOINtable2ON condition;
- Where type is one of LEFT, RIGHT or FULL
Full Outer Join in MySQL
(SELECT * FROM Student LEFT OUTER JOINEnrolment ON Student.ID = Enrolment.ID)
UNION
(SELECT * FROM Student RIGHT OUTER JOINEnrolment ON Student.ID = Enrolment.ID);
5.5 Order By
SELECT columns FROM tables
WHERE condition
ORDER BY cols [ASC|DESC]
- The ORDER BY clause sorts the results of a query
- You can sort in ascending (default) or descending order
- Multiple columns can be given
5.6 Aggregate Functions
- COUNT: The number of rows
- SUM: The sum of the entries in the column
- AVG: The average entry in a column
- MIN, MAX: The minimum/maximum entries in a column
SELECT COUNT | SUM | AVG | MIN | MAX (*) AS C FROM tablename;
- You cannot use aggregate functions in the WHERE clause
The use of aggregate functions leads to all rows afterthefirst row being truncated.
- But you can use them in the subqueries in the WHERE clause
5.7 GROUP BY
SELECT column_set1 FROM tablesWHERE predicate
GROUP BY column_set2;
- Every entry in ‘column_set2’ should be in ‘column_set1’,beaconstant, or be an aggregate function
5.8 Having
- HAVING is like a WHERE clause, except that it onlyappliesto the results of a GROUP BY query
- It can be used to select groups which satisfyagivencondition
SELECT Name,
AVG(Mark) AS Average
FROM Grades
GROUP BY Name
HAVING
AVG(Mark) >= 40;
- HAVING refers to the groups of rows, and socannotusecolumns or aggregate functions that does not exist afterthestep of column selection (see below).
5.9 UNION, INTERSECT and EXCEPT
- Only UNION is supported in MySQL. The other two can be simulated with subqueries.
- They all combine the results from two select statements
Lab1
drop table if exists `activity`, `module_enrollment`, `student`, `modules`, `teachers`;
Task 1: Creating tables
create table `teachers`
(
`id` int, -- we will add primary key later. NOT NULL and UNIQUE is not needed.
`name` varchar(200) not null,
`tel_no` varchar(40), -- not using INT here as we have numbers like +44
`office` varchar(15) not null
);
create table `modules`
(
`code` varchar(10), -- we will add primary key later. NOT NULL and UNIQUE is not needed.
`title` varchar(100) not null,
`teacher_id` int
);
create table `student`
(
`id` int(6), -- we will add primary key later. NOT NULL and UNIQUE is not needed.
`name` varchar(200) not null ,
`email` varchar(100) not null,
`enrolled_modules` varchar(255)
);
Task 2: Primary keys
alter table `teachers` add primary key (`id`);
alter table `modules` add primary key (`code`);
alter table `student` add primary key (`id`);
-- The one below is a super key. Not a good choice:
-- alter table `student` add primary key (`student_id`, `student_name`);
Task 3: Foreign key
alter table `modules` add
constraint fk_module_teacher foreign key (`teacher_id`)
references `teachers` (`id`)
Task 4: Inserting data
insert into `teachers` values (6503399, 'John Drake', '12022017202020', 'SD-766');
insert into `modules` values ('HCI-101', 'Human Computer Interaction', 6503399);
insert into `teachers` values (7614411, 'Felicia Gomez', '1024', 'BES-207');
insert into `modules` values ('HSB', 'Haskell for Beginners', 7614411);
insert into `teachers` values (5899114, 'John Cartwright', '12345 ext 1212', 'BES-201');
insert into `modules` values ('MC1', 'Mathematics', 5899114);
-- Another way to insert data, if the module is not assigned with any teacher yet,
-- you can set it to be null and update it later
insert into `modules` values ('MC2', 'Advanced Mathematics', null); -- module is not assigned with teacher yet
insert into `teachers` values (7099543, 'Dave Moe', 'BES-205', '65432 ext 2121');
update `modules` set `teacher_id` = 7099543 where `code` = 'MC2'; -- update the teacher reference
Task 5: Foreign key for student
alter table `student` add constraint fk_student_module
foreign key (`enrolled_modules`) references `modules` (`code`);
insert into `student` values (764411, 'Daryl', 'Daryl.Silly@birmmingham.ac.uk', 'MC1'); -- first module for daryl
Task 6: Second module for Dary
-- insert into `student` values (764411, 'Daryl', 'Daryl.Silly@birmmingham.ac.uk', 'MC2');
-- You will get error: [23000][1062] (conn=40) Duplicate entry '764411' for key 'PRIMARY'
Task 7: Create the activity relation
create table activity
(
`name` varchar(100) primary key,
`student_id` int(6),
`description` varchar(255),
constraint fk_activity_student foreign key (`student_id`) references `student` (`id`)
);
-- remove the primary key for student
-- alter table `student` drop primary key;
-- now try to add foreign key to `activity`.`student_id`
-- alter table `activity` add constraint fk_activity_student
-- foreign key (`student_id`) references `student` (`id`);
--
-- Does not work: Can't create table `jianjun`.`activity` (errno: 150 "Foreign key constraint is incorrectly formed")
-- Reason explained in the lab sheet
Task 8: Redesigns
drop table if exists module_enrollment, activity, student, modules;
create table `modules`
(
`code` varchar(10) primary key,
`title` varchar(100) not null,
`teacher_id` int,
constraint fk_module_teacher foreign key (`teacher_id`) references `teachers` (`id`)
);
create table `student`
(
`id` int(6) primary key,
`name` varchar(200) not null,
`email` varchar(100) not null
);
create table module_enrollment
(
`enrollment_id` int primary key, -- this column is optional.
`module_code` varchar(10),
`student_id` int(6),
constraint fk_enrollment_module foreign key (`module_code`) references `modules` (`code`),
constraint fk_enrollment_student foreign key (`student_id`) references `student` (`id`)
);
lab2
###Data
drop table if exists `activity`, `module_enrollment`, `student`, `modules`, `teachers`;
create table `teachers`
(
`id` int primary key, -- we will add primary key later. NOT NULL and UNIQUE is not needed.
`name` varchar(200) not null,
`tel_no` varchar(40), -- not using INT here as we have numbers like +44
`office` varchar(15) not null
);
create table `modules`
(
`code` varchar(10) primary key,
`title` varchar(100) not null,
`teacher_id` int,
constraint fk_module_teacher foreign key (`teacher_id`) references `teachers` (`id`)
);
create table `student`
(
`id` int(6) primary key,
`name` varchar(200) not null,
`email` varchar(100) not null
);
create table module_enrollment
(
`enrollment_id` int primary key, -- this column is optional.
`module_code` varchar(10),
`student_id` int(6),
constraint fk_enrollment_module foreign key (`module_code`) references `modules` (`code`),
constraint fk_enrollment_student foreign key (`student_id`) references `student` (`id`)
);
create table activity
(
`name` varchar(100) primary key,
`student_id` int(6),
`description` varchar(255),
constraint fk_activity_student foreign key (`student_id`) references `student` (`id`)
);
insert into `teachers` values (6503399, 'John Drake', '12022017202020', 'SD-766');
insert into `modules` values ('HCI-101', 'Human Computer Interaction', 6503399);
insert into `teachers` values (7614411, 'Felicia Gomez', '1024', 'BES-207');
insert into `modules` values ('HSB', 'Haskell for Beginners', 7614411);
insert into `teachers` values (5899114, 'John Cartwright', '12345 ext 1212', 'BES-201');
insert into `modules` values ('MC1', 'Mathematics', 5899114);
insert into `teachers` values (7099543, 'Dave Moe', 'BES-205', '65432 ext 2121');
insert into `modules` values ('MC2', 'Advanced Mathematics', 7099543);
insert into `student` values
(156123,'Nuno Bloggs','nuno@bloggs.com'),
(156897,'John Trump','John@trump.com'),
(123987,'Lidia Elliott','Lidia@gmail.com'),
(777123,'Alicia Smith','Alicia123@456.com'),
(127845,'Sophie Johns','S.Johns@nott.edu.cn');
insert into `module_enrollment` values
(1,'HSB',156123),
(2,'HCI-101',156123),
(3,'HSB',156897),
(4,'MC1',156897),
(5,'MC2',156897),
(6,'MC2',777123),
(7,'HSB',127845),
(8,'HCI-101',127845),
(9,'MC1',127845),
(10,'MC2',127845);
Task 1:
1. Print the Names of all Teachers.
select name from teachers;
2. Retrieve all students and all of the information held about them (excluding enrolment).
select * from student;
3. Retrieve the student ID’s who study MC2.
select distinct id from student, module_enrollment
where student.id = module_enrollment.student_id and module_code = 'MC2';
4. Retrieve the list of student ID’s, names and email address for students who study HCI-101.
-- Your results should not include duplicates or incorrect information.
select distinct id, name, email
from student s, module_enrollment me
where s.id = me.student_id and me.module_code = 'HCI-101';
5. Retrieve the Names of students who do not take the module ‘MC1’
select distinct s.name from student s left outer join module_enrollment me on s.id = me.student_id
where s.name not in (
select s.name from student s, module_enrollment me
where s.id = me.student_id and me.module_code = 'MC1'
)
6. Retrieve the Names and Emails of students who study both ‘MC1’ and ‘MC2’.
select s.name, s.email from student s, module_enrollment me
where s.id = me.student_id and me.module_code = 'MC1'
and s.name in (
select s.name from student s, module_enrollment me
where s.id = me.student_id and me.module_code = 'MC2'
);
select s.name, s.email from student s, module_enrollment me1, module_enrollment me2
where s.id = me1.student_id and s.id = me2.student_id
and me1.module_code = 'MC1'
and me2.module_code = 'MC2'
7. Retrieve the Names and Telephone Numbers of lecturers whose office is in the ‘BES’ building.
select name, tel_no from teachers where office like 'BES%';
Task 2:
8. Retrieve the IDs of students who take the module ‘MC1’ OR ‘MC2’.
select id from module_enrollment where module_code in ('MC1', 'MC2');
9. Retrieve the Names and Emails of students who study either in ‘MC1’ OR ‘MC2’.
select distinct s.name, s.email from student s, module_enrollment me
where s.id = me.student_id and me.module_code = 'MC2'
or s.name in (
select s.name from student s, module_enrollment me
where s.id = me.student_id and me.module_code = 'MC1'
);
10. Retrieve the Names and Emails of students whose email is not ended with ‘@gmail.com’.
select name, email from student where email not like '%gmail.com';
11. Identify if there is any student who enrolled in HSB, HCI-101 and MC2 and Retrieve the Names and ID of them.
select distinct s.name, s.email from student s, module_enrollment me1, module_enrollment me2, module_enrollment me3
where s.id = me1.student_id and s.id = me2.student_id and s.id=me3.student_id
and me1.module_code = 'HSB'
and me2.module_code = 'MC2'
and me3.module_code = 'HCI-101'
12. Retrieve the ID and Names of students whose name includes ‘ia’
select id, name from student where name like '%ia%';
13. Retrieve the Names and Emails of students who study not in ‘MC1’ OR ‘MC2’ OR ‘HCI-101’.
select distinct s.name from student s left outer join module_enrollment me on s.id = me.student_id
where s.name not in (
select s.name from student s, module_enrollment me
where s.id = me.student_id and me.module_code = 'MC1' OR s.id = me.student_id and me.module_code = 'MC2' OR s.id = me.student_id and me.module_code = 'HCI-101'
)
lab3
Data
drop table if exists cd, artist;
create table `artist` (
`artid` int primary key,
`artname` varchar(100)
);
insert into `artist` values
(6,'Animal Collective'),
(3,'Deadmau5'),
(7,'Kings of Leon'),
(4,'Mark Ronson'),
(5,'Mark Ronson & The Business Intl'),
(8,'Maroon 5'),
(2,'Mr Scruff'),
(1,'Muse');
create table `cd` (
`cdid` int primary key,
`artid` int,
`cdtitle` varchar(100),
`cdprice` double,
`cdgenre` varchar(50),
`cdnumtracks` int,
constraint fk_cd foreign key (`artid`) references `artist` (`artid`)
);
insert into `cd` values
(1,1,'Black Holes and Revelations',9.99,'Rock',NULL),
(2,1,'The Resistance',11.99,'Rock',NULL),
(3,2,'Ninja Tuna',9.99,'Electronica',NULL),
(4,3,'For Lack of a Better Name',9.99,'Electro House',NULL),
(5,4,'Version',12.99,'Pop',NULL),
(6,5,'Record Collection',11.99,'Alternative Rock',NULL),
(7,6,'Merriweather Post Pavilion',12.99,'Electronica',NULL),
(8,7,'7 Only By The Night',9.99,'Rock',NULL),
(9,7,'Come Around Sundown',12.99,'Rock',NULL),
(10,8,'Hands All Over',11.99,'Pop',NULL);
Task 1:
a. List the titles and prices of CDs in order of price from highest to lowest.
select cdtitle, cdprice from cd order by cdprice desc;
b. List the Artist Name, Titles and the Price of CDs in alphabetical order by artist name.
-- The Price of the CD should be returned in a column called ‘Full Price’ with tax (20%) included - the cd price in the database is not inclusive of tax.
select artname, cdtitle, (cdprice * 1.2) as `full price` from cd, artist where cd.artid = artist.artid;
c. List the titles, genres and prices CDs in alphabetical order by genre
-- then by price from the highest price to the lowest one.
select cdtitle, cdgenre, cdprice from cd order by cdgenre ASC, cdprice DESC;
Task 2:
a. Find the lowest price of any CD. The result should be presented in a column named ‘Cheapest CD
select cdtitle as `Cheapest CD` from cd where cdprice <= all(select cdprice from cd);
select min(cdprice) as `Cheapest CD` from cd; -- in case you have a different understanding of this question.
b. Identify the difference between the most expensive and cheapest CD.
-- The result should be presented in a column named ‘CD Price Range’.
select (max(cdprice) - min(cdprice)) as 'CD Price Range' from cd;
c. Find the number of CDs costing 9.99. The result should be presented in a column named ‘Count of £9.99 CD’s’
select (count(cdtitle)) as 'Count of $9.99 CD\'s' from cd where cdprice = 9.99;
d. Find the title of the most expensive Electronica CD(s).
select cdtitle from cd where cdprice >= all (select cdprice from cd where cdgenre = 'Electronica') and
####e. Find the number of different Prices in the CD table.
select count(distinct cdprice) from cd;
f. List all the information about the cheapest (lowest priced) CDs.
select * from cd, artist where cd.artid = artist.artid and cdprice <= all(select cdprice from cd);
Task 3:
a. Find a list of artist names, the number of CDs they have produced, and the average price for their CDs.
-- Only return results for artists with more than one CD. The results should be in the following format:
-- || Artist | Average CD Price | Number of CD’s ||
select artist.artname as `Artist`, AVG(cdprice) as `Average CD price`, COUNT(cdid) as `Number of CDs`
from cd, artist where cd.artid = artist.artid group by artist.artname having `Number of CDs` > 1;
b. Find a list of artist names, the number of CDs by that artist and the average price for their CDs
-- but not including ‘Electronica’ albums (you might like to use a WHERE in this one too).
-- The results should be in the following format:
-- || Artist | Average CD Price | Number of CD’s ||
select artist.artname as `Artist`, AVG(cdprice) as `Average CD price`, COUNT(cdid) as `Number of CDs`
from cd, artist where cd.artid = artist.artid and cdgenre <> 'Electronica' group by artist.artname;