MS ACCESS -- SQL

SELECT school_name, count(student_school_id) AS n_of_student
FROM tbl_student
GROUP BY school_name;

SELECT school_name, count(student_school_id) AS n_of_student, avg(selected_response_score) as mean_score, STDEV(selected_response_score) as SD
FROM tbl_student
GROUP BY school_name;

 

Apple ID recovery

I have an old iphone that I want to use through  my home Wifi for skyping and zooming (www.zoom.us).  It is not connected at all to the phone network system.

The phone worked fine for a while for my limited purposes, but just this morning, it gave me a message "Activation Required" to which  I had to provide my apple ID and password. This happened after ignoring messages that were reminding me to enter a password to the apple account.

(My phone's start button is broken, so I couldn't restart it.)

I didn't remember my password.  The following is what I was told by the customer service representative.

Go to appleid.apple.com

Click on:

Forgot Apple ID or password?

Enter your Apple ID to get started.

You will be going through the pages where you are expected to  enter information regarding i-phone.   Most of these options require that you are an active i-phone user or you have an Apple device.  I don't, which is why I had to call Apple.   If you look close, there is an option (not highlighted by a big icon, so it's easy too miss) for people who don't use any  apple products.  Just find that  and follow instructions.  They will automatically text you a code to your current phone and you will be entering that to the website form.

The activation of a new password will be about 24 hours and this is where I am now.

To get a telephone support (like I did to get information), you will have to navigate from this page.  You enter your phone number and they will call you in two minutes.

https://getsupport.apple.com/?caller=cups

Lots of R codes

My notes no DPLYR package.

This is how you read in dplyr:

library(dplyr)

 

I think the following requires dplyr, but I am not 100% sure.

Sorting can be done in this way.  "desc" means descending.

arrange(sales, desc(profit),desc(units))

 

 

 

 

How to modify .htaccess file

How do I modify the .htaccess to do this?

When people try to find this:

https://www.example.com/example/archives/6151#comments

I want them to go to:

https://www.example.com/archives/6151#comments

 

 

MySQL database character set and collation

The default character set is latin1_swedish_ci .  I will use utf8_general_ci instead since it seems to be the most up-to-date character set and the article I found (see reference) suggests we do.  However, I have never encountered an issue just using latin1_swedish_ci for Japanese materials.  My questions are:

  1. Can I change it back to other options later?
  2. What does "ci" mean?
  3. There are many types of utf8 in the option.  Is utf8_general_ci really the good option?

Reference:

https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation

 

SQL basics

 

I need quotes when specifying a date value.

SELECT *
FROM purchases
WHERE purchased_at <= "2018-11-01";

 

Pick up rows whose values contain "pudding":

SELECT *
FROM purchases
WHERE name like "%pudding%";

 

You can use NOT:

SELECT *
FROM purchases
WHERE NOT character_name="Ken";

 

Another example of NOT:

SELECT *
FROM purchases
WHERE NOT name like "%pudding%";

 

 

SELECT *
FROM purchases
WHERE price IS NOT NULL;

 

Ordering observations

SELECT *
FROM purchases
WHERE character_name = "Ken"
ORDER BY price DESC;

 

NOT

SELECT *
FROM purchases
WHERE NOT character_name = "Ken";

 

SELECT *
FROM purchases
WHERE price IS NULL;

 

SELECT *
FROM purchases
ORDER BY price DESC
LIMIT 5;

 

Counting the row of observations

This counts  non-missing values.

SELECT COUNT(name)
FROM purchases;

This counts the number of rows.

SELECT COUNT(*)
FROM purchases;

Using the where statement:

SELECT COUNT(*)
FROM purchases
WHERE character_name="Ken"
;

Picking up the observation whose value is a maximum value

SELECT name, max(price)
FROM purchases
WHERE character_name="Ken"
;

GET THE SUM PER GROUP

SELECT SUM(price), purchased_at
FROM purchases
GROUP BY purchased_at
;

 

SELECT COUNT(*), purchased_at
FROM purchases
GROUP BY purchased_at;

 

SELECT SUM(price), purchased_at
FROM purchases
WHERE character_name="Ken"
GROUP BY purchased_at

;

 

SELECT SUM(price), purchased_at
FROM purchases
GROUP BY purchased_at
HAVING sum(price) > 20;

 

This picks up cases with a condition (in this case, whoever had a higher score than the guy Will).

SELECT name
FROM players
WHERE goals > (
-- Write an SQL statement below to get Will's score
SELECT goals
FROM players
WHERE name = "Will"
)
;

 

 

SELECT name,goals
FROM players
WHERE goals > (
SELECT AVG(goals)
FROM players
)
;

 

Using AS:

SELECT name AS "180 cm or taller"
FROM players
WHERE height >= 180
;

 

SELECT SUM(goals) AS "total team score"
FROM players
;

 

SELECT *
FROM countries
WHERE rank < (
SELECT rank
FROM countries
WHERE name="Japan"
)
;

 

Merging two tables:

SELECT *
FROM players
-- Add a name to the combined table
JOIN countries
-- Add a join condition
ON players.country_id = countries.id
;

 

 

SELECT players.name, countries.name
FROM players
JOIN countries
ON players.country_id = countries.id
;

 

SELECT countries.name, SUM(goals)
FROM players
JOIN countries
ON players.country_id = countries.id
GROUP BY countries.name
;

 

 

SELECT *
FROM players
JOIN teams
ON players.previous_team_id = teams.id
;

 

SELECT players.name AS "player name", teams.name AS "team (last year)"
FROM players
JOIN teams
ON players.previous_team_id = teams.id
;

SELECT *
FROM players
LEFT JOIN teams
ON players.previous_team_id =teams.id
;

 

SELECT players.name AS "player name", teams.name AS "team (last year)"
FROM players
LEFT JOIN teams
ON players.previous_team_id = teams.id
;

SELECT *
FROM players
JOIN countries
ON players.country_id = countries.id
LEFT JOIN teams
ON players.previous_team_id = teams.id
;

 

SELECT players.name AS "Player name", players.height AS "height"
FROM players
WHERE height > (
SELECT AVG(height)
FROM players
)

 

select name, price
from items
order by price desc
;

 

-- get all rows that contain the string "shirt"
SELECT *
FROM items
WHERE name like "%shirt%"
;

 

SELECT name, price, MAX(price - cost)
FROM items;

 

SELECT name, price
FROM items
WHERE price > (
SELECT price
FROM items
WHERE name = "grey hoodie"
);