Hello im new to this site I have some questions to ask. Thank you.
What does this do? NVL (m-date), to-char(sysdate)
samedi 27 juin 2015
MySQL Case sensitive insert
I have created a database with case sensitive character set
Create database Grupo88 character set utf8 collate utf8_bin;
CREATE TABLE Grupo88.Usuarios(
nombreUsuario varchar(30) primary key,
clave varchar(120) not null);
INSERT INTO usuarios(nombreUsuario,clave)
VALUES('guy','pass');
INSERT INTO usuario(nombreUsuario, clave)
VALUES('Guy', 'password');
The first insert goes well, but the second one says that the value "Guy" already exists. Setting my database to be case sensitive is not enough? How can I do to allow case sensitive inserts?
Thanks
MYSQL limit returns unexpected results
<?php
$quuuu = mysql_query("SELECT * FROM products limit 9,15 ") or die("Query Error");
while($ffff=mysql_fetch_array($quuuu)){
echo "<li><a href='view.php?id=" . $ffff['id'] . "'>" . $ffff['title'] . "</a></li>";
}
echo mysql_num_rows($quuuu);
?>
its should return (7), and the result is (15)
SQL JOIN to get two records for each row
I have two tables, say
TABLE: Transactions
COLUMNS: sender_id, receiver_id, value
and
TABLE: Users
COLUMNS: user_id, username
Is it possible to make a SQL statement to JOIN two rows from the users table for each record in the transactions table? So for each result row, I should have two usernames from the users table, but with two different aliases. How to achieve this?
Thanks
How do I add records from an Access database into an existing MSSQL database?
I have an existing Access database that has out grown it's usefulness. I have written a Visual Basic program that uses a SQL database and have been re-entering the data from the Access file into the SQL file individually. I still have 300+ records to move and would like to find a way to do all this using either a data snippet in Visual Basic, or using SQL Express. I'm smart enough to set up and use Access, and smart enough to Create a program and database in Visual Basic and SQL but for some reason I not smart enough to move the records between the 2 databases. When I originally created the .mdf file I attempted to move the records at that time but everything I tried didn't work so I figured I'd get it later, but now I have 300+ records in the .mdf file and need to get the others moved over. Anybody have any ideas?
Selecting max values of a column using the max value of another column
Hi folks I'm writing a query in MYSQL and it has challenged me. Here is the situation;
Let's assume I have a table named 'status' which keeps data for a tv show like that;
+---------+---------+---------+---------+
| id | season | episode | channel |
+---------+---------+---------+---------+
| 1 | 2 | 10 | a |
| 1 | 3 | 2 | b |
| 1 | 2 | 9 | c |
| 1 | 3 | 1 | d |
| 1 | 3 | 2 | e |
+---------+---------+---------+---------+
I want to retrieve the rows which contains the last released episode of the last season. According to the table above, I expect a result like that
+---------+---------+---------+
| season | episode | channel |
+---------+---------+---------+
| 3 | 2 | b |
| 3 | 2 | e |
+---------+---------+---------+
The max value of the season column is 3. In this case, I have retrieved the rows that have the max value of the episode column where season is equal to 3. I have written a query and it gives the expected result, but I don't think that it is an appropriate query. How could I improve the query below? If needed, I can add extra information or give further examples.
SELECT season,
episode,
channel
FROM `status`
WHERE `tvseriesid` = 1
AND `season` = (SELECT Max(season) AS Son
FROM `status`
WHERE `tvseriesid` = 1)
AND `episode` = (SELECT Max(episode)
FROM `status`
WHERE `tvseriesid` = 1
AND `season` = (SELECT Max(season) AS Son
FROM `status`
WHERE `tvseriesid` = 1))
how do I create a user for internet and administration on MS SQL?
I am a knew to creating users on MS SQL. So Here is what I am trying to do.
I am a master user on the Database. I can create users and databases and whatever.
I want to create two more user
1) Administrator2: This user will execute all tasks or creating, dropping, modifying data, tables and procedures on the database. It would be the same as the db_owner, however this user CANNOT be able to drop the database. (My last admin dropped my database by mistake, I know not a very good admin).
2) WebUser: This user will be used by my application so it must be able to read, write, execute procedures, etc.
Is there a pre-made schema or something for me to achieve this? If not how can I achieve this?
I already know that I have to create Logins for the server access and users for the Database. But a good simple steps would be appreciated.
MySQL keeps complaining about foreign key. Error 150
I'm trying to create a table with a foreign key to itself. I want to model a category that can be a child of another category. Here is my SQL:
CREATE TABLE IF NOT EXISTS `recipes`.`category` (
`id` INT NOT NULL COMMENT '',
`name` VARCHAR(60) NOT NULL COMMENT '',
`description` VARCHAR(255) NULL COMMENT 'Description of the recipe category.',
`parent_id` INT NULL COMMENT '',
PRIMARY KEY (`id`) COMMENT '',
CONSTRAINT `parent_id`
FOREIGN KEY (`id`)
REFERENCES `recipes`.`category` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
However, MySQL keeps giving me an error:
Can't create table 'recipes.category' (errno: 150)
I'm trying to find out what I'm doing wrong, can anyone give me a hint? Docs say that:
Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.
However, this does not help me much.
Combining 2 queries - getting column names in one and using results in another query
Building my first MS Access SQL queries. That should not be this hard!
I have 2 tables:
A user belonging to GroupA logged in. I want to show him only those Data table rows and columns which GroupA is assigned to, like this:
╔════════╦════════╦════════╗
║ Group ║ Data3 ║ Data4 ║
╠════════╬════════╬════════╣
║ GroupA ║ 9 ║ 4 ║
╠════════╬════════╬════════╣
║ GroupA ║ 1 ║ 5 ║
╚════════╩════════╩════════╝
I tried this silly option:
SELECT (select Data from AccessRights where GroupA = "y")
FROM Data
WHERE Data.Group = "GroupA";
How to use data from stored procedure into second procedure?
The last few months I learned so much thanks to all the topics on the forum, thanks to everybody taking the effort answering them. They helped me to create my procedures but now I'm stuck. And related topics haven't given me a solution so far.
Also looked at this page: http://ift.tt/1hyhgrb
Let me explain the situation:
I have created two procedures they work perfectly. They work with temperary tables.
Below I have written out as specific as possible (I hope). If further information is needed I can give this of course.
The two procedures:
1 - Calculations of costs made by own drivers, trucks, fuel consumption > named CostOM
2 - Calculations of revenue and costs on the trip (Revene of orders, Haulage cost, toll cost etc.) > named Trip_margin
What I need to add to the second procedure (Trip_margin) is the calculated amount from the first procedure (CostOM) that is in a column named 'CostOwnMat' (which stands for cost of own material).
Both procedures use the same primary key structure and that is the tripnumber.
They only difference is that procedure 1 can have the same tripnumber multiple times because a trip can be done be 2 or more drivers.
So I need the total sum of the 'CostOwnMat' and return this in the second procedure in a new column named 'total_cost_OM'.
When I have that then I have al the revenue, costs etc. on the trip needed to calculate the margin made on the trip.
I think below is the direction of the solution but how to apply this?
• Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.
• Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.
• Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.
• Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the server is restarted.
How to loop through many to many?
I have a query that returns data like this:
+-----------+---------------+---------------+--------------+
| recipe_id | name | ingredient_id | item |
+-----------+---------------+---------------+--------------+
| 1 | Apple Crumble | 1 | apple |
| 1 | Apple Crumble | 5 | flour |
| 1 | Apple Crumble | 7 | butter |
| 1 | Apple Crumble | 8 | sugar |
| 2 | Fruit Salad | 6 | fruit juice |
| 2 | Fruit Salad | 2 | banana |
| 2 | Fruit Salad | 1 | apple |
| 2 | Fruit Salad | 3 | kiwi fruit |
| 2 | Fruit Salad | 4 | strawberries |
+-----------+---------------+---------------+--------------+
How do i loop through and remove the duplicate records but keep the items for each? so something like this:
1 Apple crumble 1 apple, 5 flour, ... .. ..
How to import PHPMyAdmin SQL Dump File into mySQL DB?
I know how to import .sql files into mysql using the following command:
mysql -u root -p <db-name> < <sql-file>
But I am now looking at a PHP project which come with the following file: databases.php.
How do I import this file (i.e apply all the SQL statements in it) to mySQL?
The databases.php includes the following SQL:
-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://ift.tt/HXFLWR
--
-- Host: localhost
-- Generation Time: Aug 05, 2010 at 06:37 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `chapter11`
--
-- --------------------------------------------------------
--
-- Table structure for table `comments`
--
CREATE TABLE IF NOT EXISTS `comments` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`comment` longtext NOT NULL,
`profile_post` int(11) NOT NULL,
`creator` int(11) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`approved` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`ID`),
KEY `profile_post` (`profile_post`,`creator`,`approved`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `comments`
--
INSERT INTO `comments` (`ID`, `comment`, `profile_post`, `creator`, `created`, `approved`) VALUES
(1, 'This is a test comment', 1, 1, '2010-05-13 18:01:29', 1);
-- --------------------------------------------------------
--
-- Table structure for table `controllers`
--
CREATE TABLE IF NOT EXISTS `controllers` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`controller` varchar(255) NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`ID`),
UNIQUE KEY `controller` (`controller`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
--
-- Dumping data for table `controllers`
--
INSERT INTO `controllers` (`ID`, `controller`, `active`) VALUES
(1, 'authenticate', 1),
(2, 'members', 1),
(3, 'relationship', 1),
(4, 'relationships', 1),
(5, 'profile', 1),
(6, 'calendar', 1),
(7, 'stream', 1),
(8, 'messages', 1),
(9, 'groups', 1),
(10, 'group', 1),
(11, 'api', 1);
-- --------------------------------------------------------
--
-- Table structure for table `events`
--
CREATE TABLE IF NOT EXISTS `events` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`creator` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`description` longtext NOT NULL,
`event_date` date NOT NULL,
`start_time` time NOT NULL,
`end_time` time NOT NULL,
`type` enum('public','private') NOT NULL,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `events`
--
-- --------------------------------------------------------
--
-- Table structure for table `event_attendees`
--
CREATE TABLE IF NOT EXISTS `event_attendees` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`event_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`status` enum('invited','going','not going','maybe') NOT NULL,
PRIMARY KEY (`ID`),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `event_attendees`
--
-- --------------------------------------------------------
--
-- Table structure for table `groups`
--
CREATE TABLE IF NOT EXISTS `groups` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` longtext NOT NULL,
`creator` int(11) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`type` enum('public','private','private-member-invite','private-self-invite') NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `groups`
--
INSERT INTO `groups` (`ID`, `name`, `description`, `creator`, `created`, `type`, `active`) VALUES
(1, 'test group', 'test group about xyz', 1, '2010-08-02 01:15:53', 'public', 1),
(2, 'Dinosaur Activities in the North East', 'Group dedicated to the promotion of dinosaur friendly activities in the North East of England', 1, '2010-08-02 02:14:24', 'public', 1);
-- --------------------------------------------------------
--
-- Table structure for table `group_membership`
--
CREATE TABLE IF NOT EXISTS `group_membership` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`group` int(11) NOT NULL,
`user` int(11) NOT NULL,
`approved` tinyint(1) NOT NULL DEFAULT '0',
`requested` tinyint(1) NOT NULL DEFAULT '0',
`invited` tinyint(1) NOT NULL DEFAULT '0',
`requested_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`invited_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`join_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`inviter` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `group_membership`
--
INSERT INTO `group_membership` (`ID`, `group`, `user`, `approved`, `requested`, `invited`, `requested_date`, `invited_date`, `join_date`, `inviter`) VALUES
(1, 2, 2, 1, 0, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 0);
-- --------------------------------------------------------
--
-- Table structure for table `ikes`
--
CREATE TABLE IF NOT EXISTS `ikes` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`type` enum('likes','dislikes') NOT NULL,
`status` int(11) NOT NULL,
`iker` int(11) NOT NULL,
`iked` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `ikes`
--
INSERT INTO `ikes` (`ID`, `type`, `status`, `iker`, `iked`) VALUES
(1, 'likes', 6, 1, '2010-06-23 00:15:32'),
(2, 'dislikes', 6, 2, '2010-06-23 00:15:32'),
(3, 'likes', 6, 3, '2010-06-23 00:15:38');
-- --------------------------------------------------------
--
-- Table structure for table `messages`
--
CREATE TABLE IF NOT EXISTS `messages` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`sender` int(11) NOT NULL,
`recipient` int(11) NOT NULL,
`sent` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`read` tinyint(1) NOT NULL,
`subject` varchar(255) NOT NULL,
`message` longtext NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `messages`
--
INSERT INTO `messages` (`ID`, `sender`, `recipient`, `sent`, `read`, `subject`, `message`) VALUES
(1, 2, 3, '2010-06-27 23:19:41', 1, 'test', 'test msg'),
(4, 2, 1, '2010-06-04 16:26:29', 1, 'Saturday?', 'Are you still up for going hill walking with Mr. Glen on Saturday; let me know if you do need to borrow my t-rex leash, as I have a spare one.\r\n<br />\r\nCheers,<br />\r\nRick'),
(3, 2, 1, '2010-06-01 16:25:57', 1, 'Check out this link', ''),
(5, 3, 1, '2010-06-10 16:26:42', 1, 'Hi', ''),
(6, 1, 2, '2010-06-30 17:12:27', 0, 'Re: Saturday?', 'Yes!');
-- --------------------------------------------------------
--
-- Table structure for table `posts`
--
CREATE TABLE IF NOT EXISTS `posts` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`topic` int(11) NOT NULL,
`post` longtext NOT NULL,
`creator` int(11) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `posts`
--
INSERT INTO `posts` (`ID`, `topic`, `post`, `creator`, `created`) VALUES
(1, 1, 'We are planning on arranging a regular walk around the riverside park with our T-Rex''s - anyone want to join us?', 1, '2010-07-15 12:20:22'),
(2, 2, 'This is another new topic', 1, '2010-07-20 02:50:52'),
(3, 0, 'this is a test reply', 1, '2010-08-02 03:08:51'),
(4, 2, 'this is a test reply', 1, '2010-07-21 02:50:52');
-- --------------------------------------------------------
--
-- Table structure for table `profile`
--
CREATE TABLE IF NOT EXISTS `profile` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`dino_name` varchar(255) NOT NULL,
`dino_dob` varchar(255) NOT NULL,
`dino_breed` varchar(255) NOT NULL,
`dino_gender` varchar(255) NOT NULL,
`photo` varchar(255) NOT NULL,
`bio` longtext NOT NULL,
`user_dob` date NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `profile`
--
INSERT INTO `profile` (`user_id`, `name`, `dino_name`, `dino_dob`, `dino_breed`, `dino_gender`, `photo`, `bio`, `user_dob`) VALUES
(1, 'Michael Peacock', 'Mr Glen', '01/01/1990', 'T-Rex', 'male', 'n663170160_722.jpg', 'I''m a web developer from the North East of England, running web design agency Peacock Carter a team of 4 Internet specialists. I''ve also written a number of books, including, PHP 5 E-Commerce Development, Drupal 6 Social Networking, Selling Online with Drupal e-Commerce and Building Websites with TYPO3.', '1987-07-28'),
(2, 'Richard Thompson', 'Stu Fishman', '', 'stegosaurus', 'male', '', '', '1910-06-01');
-- --------------------------------------------------------
--
-- Table structure for table `relationships`
--
CREATE TABLE IF NOT EXISTS `relationships` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`type` int(11) NOT NULL,
`usera` int(11) NOT NULL,
`userb` int(11) NOT NULL,
`accepted` tinyint(1) NOT NULL,
PRIMARY KEY (`ID`),
KEY `type` (`type`,`usera`,`userb`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `relationships`
--
INSERT INTO `relationships` (`ID`, `type`, `usera`, `userb`, `accepted`) VALUES
(1, 3, 1, 2, 1);
-- --------------------------------------------------------
--
-- Table structure for table `relationship_types`
--
CREATE TABLE IF NOT EXISTS `relationship_types` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`plural_name` varchar(255) NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`mutual` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `relationship_types`
--
INSERT INTO `relationship_types` (`ID`, `name`, `plural_name`, `active`, `mutual`) VALUES
(1, 'Friend', 'friends', 1, 1),
(2, 'Colleague', 'colleagues', 1, 1),
(3, 'Jogging buddy', 'Jogging buddies', 1, 1);
-- --------------------------------------------------------
--
-- Table structure for table `settings`
--
CREATE TABLE IF NOT EXISTS `settings` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`key` varchar(255) NOT NULL,
`value` longtext NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `settings`
--
INSERT INTO `settings` (`ID`, `key`, `value`) VALUES
(1, 'view', 'default'),
(2, 'sitename', 'DINO SPACE!'),
(3, 'siteurl', 'http://localhost/mkpbook5/trunk/chapter11/'),
(4, 'captcha.enabled', '0'),
(5, 'upload_path', 'c:/wamp/www/mkpbook5/trunk/chapter11/uploads/');
-- --------------------------------------------------------
--
-- Table structure for table `statuses`
--
CREATE TABLE IF NOT EXISTS `statuses` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`update` longtext NOT NULL,
`type` int(255) NOT NULL,
`poster` int(11) NOT NULL,
`profile` int(11) NOT NULL,
`posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
KEY `poster` (`poster`,`profile`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;
--
-- Dumping data for table `statuses`
--
INSERT INTO `statuses` (`ID`, `update`, `type`, `poster`, `profile`, `posted`) VALUES
(1, 'Test ABC', 1, 1, 0, '2010-05-13 17:40:52'),
(2, 'Look at this', 0, 1, 0, '2010-05-02 12:31:20'),
(3, 'Test - 1.2.3.4', 1, 1, 1, '2010-05-13 17:41:03'),
(4, 'This is an update on someones profile', 1, 2, 1, '2010-06-02 21:53:34'),
(5, 'This is another update on someones profile', 1, 1, 2, '2010-06-02 21:53:37'),
(6, 'Nice to see you on here!', 1, 3, 1, '2010-06-22 22:20:43'),
(12, 'Taking my Dino out for a walk', 1, 1, 1, '2010-06-27 21:30:10'),
(32, 'Dinosaurs! I loved this show!', 3, 1, 1, '2010-07-02 23:38:39'),
(33, 'Really useful site!', 4, 1, 1, '2010-07-02 23:52:56'),
(30, 'I''m on stage rehearsing!', 2, 1, 1, '2010-07-02 23:02:42');
-- --------------------------------------------------------
--
-- Table structure for table `statuses_images`
--
CREATE TABLE IF NOT EXISTS `statuses_images` (
`id` int(11) NOT NULL,
`image` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `statuses_images`
--
INSERT INTO `statuses_images` (`id`, `image`) VALUES
(30, '1278108160_2.JPG');
-- --------------------------------------------------------
--
-- Table structure for table `statuses_links`
--
CREATE TABLE IF NOT EXISTS `statuses_links` (
`id` int(11) NOT NULL,
`URL` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `statuses_links`
--
INSERT INTO `statuses_links` (`id`, `URL`, `description`) VALUES
(33, 'http://ift.tt/O2rMYp', 'T-Rex on Wikipedia');
-- --------------------------------------------------------
--
-- Table structure for table `statuses_videos`
--
CREATE TABLE IF NOT EXISTS `statuses_videos` (
`id` int(11) NOT NULL,
`video_id` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `statuses_videos`
--
INSERT INTO `statuses_videos` (`id`, `video_id`) VALUES
(32, 'BkAEH6uX7hQ');
-- --------------------------------------------------------
--
-- Table structure for table `status_types`
--
CREATE TABLE IF NOT EXISTS `status_types` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`type_name` varchar(100) NOT NULL,
`type_reference` varchar(50) NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`type_name_other` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `type_reference` (`type_reference`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `status_types`
--
INSERT INTO `status_types` (`ID`, `type_name`, `type_reference`, `active`, `type_name_other`) VALUES
(1, 'Changed their status to', 'update', 1, ''),
(2, 'Posted an image', 'image', 1, ''),
(3, 'Uploaded a video', 'video', 1, ''),
(4, 'Posted a link', 'link', 1, '');
-- --------------------------------------------------------
--
-- Table structure for table `topics`
--
CREATE TABLE IF NOT EXISTS `topics` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`creator` int(11) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`active` tinyint(1) NOT NULL DEFAULT '1',
`group` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `topics`
--
INSERT INTO `topics` (`ID`, `name`, `creator`, `created`, `active`, `group`) VALUES
(1, 'Walk through Riverside Park, Chester-le-Street', 1, '2010-07-15 12:20:22', 1, 2),
(2, 'This is another new topic', 1, '2010-07-20 02:50:52', 1, 2);
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`password_hash` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`password_salt` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '0',
`admin` tinyint(1) NOT NULL DEFAULT '0',
`banned` tinyint(1) NOT NULL DEFAULT '0',
`reset_key` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`reset_expires` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`ID`, `username`, `password_hash`, `password_salt`, `email`, `active`, `admin`, `banned`, `reset_key`, `reset_expires`, `deleted`) VALUES
(1, 'michael', '5f4dcc3b5aa765d61d8327deb882cf99', '', 'mkpeacock@gmail.com', 1, 0, 0, '', '0000-00-00 00:00:00', 0),
(2, 'rich__t', '5f4dcc3b5aa765d61d8327deb882cf99', '', '', 1, 0, 0, '', '2010-04-01 00:19:39', 0);
Average elapsed days between two dates at customer level
I'm working on a way to average elapsed days between two dates on a customer level in SQL Server.
SELECT
CUSTOMER,
VISIT_ID,
DAYIN_DATE,
DAYOUT_DATE,
RANK () OVER (PARTITION BY CUSTOMER ORDER BY VISIT_ID DESC) AS RANKING,
LAG(DAYOUT_DATE, 1)
OVER (PARTITION BY CUSTOMER ORDER BY VISIT_ID DESC) AS DATEOUT_DT,
LEAD(DAYIN_DATE, 1)
OVER (PARTITION BY CUSTOMER ORDER BY VISIT_ID DESC) AS DATEIN_DT,
(DATEIN_DT - DATEOUT_DT) AS LATENCY
FROM VISIT_TABLE
GROUP BY 1,2,3,4;
This code takes the customer and visit(unique id), then ranks them by the DAYIN_DATE/DAYOUT_DATE. I've written a Lag and Lead function to help calculate the latency but, when averaged it doesn't average on a customer level.
What am I missing?
Select multiple rows with multiple matching id(s)
Is there a way to query data to display multiple rows with multiple selector?
like:
SELECT * FROM `book` WHERE `book`.id = 1 AND `book`.id = 2;
table:
id name
1 book1
2 book2
3 book3
4 book4
I dun suppose looping for each id is favourable.
How to avoid nested query in PostgreSQL
I managed to write this query:
INSERT INTO UserRoles (UserId, RoleId)
VALUES (@UserId, (SELECT Id FROM Roles WHERE Name = @Name));
And it works as intended. The problem is that I feel like this is a bad approach to write nested queries (here - find the id of a role in Roles table that matches the specified name). Is it possible to make this without the nested query?
Experienced programmers may laugh, but I am trying to learn this and I don't know how could I make this query better. I would be very grateful for your advice.
Converting DOB fromat
I have a table called NAMES that has a DOB column in nvarchar which for some reason will not convert to date, which is fine as long as I can run a snippet of code to convert it which is actually preferable. So here is the dilemma... some dates are m/d/yyyy and some are mm/dd/yyyy .
Examples:
2/4/1986
10/8/1999
4/24/1988
12/13/1975
I'd like to run a snippet of code to convert everything to a standard mm/dd/yyyy format. Thank you in advance...
Is this code in a stored procedure sql-injectible
If I have a where clause in a stored procedure that concatenates a search string that comes from the front end (user supplied) ... like this:
FirstName like '%'+@SearchString+'%'
Can this me injected with malicious code?
How can i migrate Postgres 9.3.5 to 9.4.4 after deleting the 9.3.5 oldbindir?
I'm trying to migrate from Postgres 9.3.5 to 9.4.4 and in doing so I've removed the old 9.3.5 install of Postgres (running brew cleanup too early did this i think). Whilst following various sources info on how to upgrade I realise that in order to pg_upgradeI need the oldbindir to do so.
Is there any way around this and if so how. If not how can i reinstall the old version in the right way into the right location in order to then run pg_upgrade?
Some context to help you understand where I am:
brew info postgres
postgresql: stable 9.4.4 (bottled)
Object-relational database system
http://ift.tt/1cHT2tT
Conflicts with: postgres-xc
/usr/local/Cellar/postgresql/9.4.4 (3014 files, 40M) *
Poured from bottle
From: http://ift.tt/1CBlqEP
==> Dependencies
Required: openssl ✔, readline ✔
==> Options
--32-bit
Build 32-bit only
--with-dtrace
Build with DTrace support
--with-python
Build with python support
--without-perl
Build without Perl support
--without-tcl
Build without Tcl support
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
http://ift.tt/1h8DBZx
greps return the following so server seems not to be running locally:
ps aux | grep postgres
501 1228 367 0 10:37am ttys000 0:00.01 grep postgres
ps aux | grep pgsql
501 1260 367 0 10:42am ttys000 0:00.01 grep pgsql
In /usr/local/Cellar/postgresql I have only; 9.4.4
In /usr/local/var I currently have:
postgres.old - where all of my old databases reside. postgres.old2 - unsure of where this came from tbh, I had to chown it to get to look at contents, only server.log inside. postgres9.4. - Full seemingly as it should be after having been initdb'd. 'postgres' - Empty, think this has been created in a failed pg_upgrade attempt.
Any help hugely appreciated, I've been on this for many hours now. Any more context info you require to help find the right solution out of this, just let me know. Thanks
Query from same table to extract different data
In a single table I have 3 columns. First defines a sector, second count and third amount. I need to extract 5 columns of data in the following manner. First column sector. Second and third to contains the values were amount is less than count and third and four to display were amount is more than count in the specific sectors. How should my query look?
Advanced mysql query which sort down specific records on result set irrespective of its default sorting?
I have a query which actually have a sorting using order by clause. i have a table like following...
user_id user_name user_age user_state user_points
1 Rakul 30 CA 56
2 Naydee 29 NY 144
3 Jeet 40 NJ 43
.....
i have following query...
select * from users where user_state = 'NY' order by user_points desc limit 50;
This gives me the list of 50 people with most points. I wanted to give least preference to few people who's id's were known. Incase if i do not have enough 50 records then those id's should come in the last in the list. I do not want the users 2 and 3 to come on top of the list even though they have higher points... those people should come on the last of the list from the query. Is there any way to push specific records to last on result set irrespective of query sorting ?
Inscription à :
Articles (Atom)